Remove Duplicates from range of cells in excel vba

Question

I'm trying to remove duplicates in excel 2013 VBA. but I'm getting error "object does not support this property or method". The problem is I don't have static range to select. I want remove duplicates from the column heaader 'abcd'.

Cells.Find(what:="abcd").Activate
ActiveCell.EntireColumn.Select
Set rng = Selection
ActiveSheet.rng.RemoveDuplicates

Answer 1

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.