Remove duplicates from cell range in excel vba

Question

I am trying to remove duplicates in excel 2013 VBA. but I get the error "the object does not support this property or method". The problem is that I do not have a static range for selection. I want to remove duplicates from the heaader 'abcd' column.

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

Answer 1

You need to tell the Range.RemoveDuplicates method which column to use. In addition, since you stated that you have a title bar, you must specify the .RemoveDuplicates method.

 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 

The source code seemed to want to remove duplicates from one column while ignoring the surrounding data. This scenario is not typical, and I included the surrounding data so that the .RemoveDuplicates process does not scramble your data. Post a comment if you really want to isolate the RemoveDuplicates process to one column.

Answer 2

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 a header use Header:=xlYes

Increase your range as per your requirement.
you can do this up to 1000 as follows:

ActiveSheet.Range("$A$1:$A$1000")

More here