Remove Duplicates from range of cells in excel vba

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 WithEnd 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.

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

If you got only one column in the range to clean, just add "(1)" to the end. It indicates in wich column of the range Excel will remove the duplicates.Something like:

 Sub norepeat()    Range("C8:C16").RemoveDuplicates (1)End Sub

Regards