This works, but not with a Structured Table. Excel manages and protects the Structured Table range.
Option Explicit
Sub sRemoveDuplicates()
Dim sh As Worksheet
Set sh = Sheets("Sheet1")
Dim lLR As Long, lLC As Long, i As Long
Dim rDelete As Range
With sh
lLR = .Cells(.Rows.Count, 1).End(xlUp).Row
lLC = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, lLC + 2), Cells(lLR, lLC + 2)).Formula _
= "=IF(COUNTIF($A$2:$A3,$A3)>1,""delete"","""")"
For i = 2 To lLR
If .Cells(i, lLC + 2) = "delete" Then
If rDelete Is Nothing Then
Set rDelete = .Cells(i, 1)
Else
Set rDelete = Union(rDelete, .Cells(i, 1))
End If 'rDelete Is Nothing
End If '.Cells(i, lLC + 2) = "delete"
Next 'i
End With 'sh
If Not rDelete Is Nothing Then
rDelete.EntireRow.Delete
End If
With sh
.Cells(1, lLC + 2).EntireColumn.Delete
End With 'sh
End Sub
Regards, TMS
Bookmarks