Remove Duplicates from range of cells in excel vba
To remove duplicates from a single column
Sub removeDuplicate()
'removeDuplicate Macro
Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$117").RemoveDuplicates Columns:=Array(1), _
Header:=xlNo
Range("A1").Select
End Sub
if you have header then use Header:=xlYes
Increase your range as per your requirement.
you can make it to 1000 like this :
ActiveSheet.Range("$A$1:$A$1000")
More info here here
You need to tell the Range.RemoveDuplicates method what column to use. Additionally, since you have expressed that you have a header row, you should tell the .RemoveDuplicates method that.
Sub dedupe_abcd()
Dim icol As Long
With Sheets("Sheet1") '<-set this worksheet reference properly!
icol = Application.Match("abcd", .Rows(1), 0)
With .Cells(1, 1).CurrentRegion
.RemoveDuplicates Columns:=icol, Header:=xlYes
End With
End With
End Sub
Your original code seemed to want to remove duplicates from a single column while ignoring surrounding data. That scenario is atypical and I've included the surrounding data so that the .RemoveDuplicates process does not scramble your data. Post back a comment if you truly wanted to isolate the RemoveDuplicates process to a single column.