12

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
Hideandseek
  • 271
  • 1
  • 4
  • 17

3 Answers3

15

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.

  • Thanks for the reply. I want duplicates to be removed from surrounding data too. But your code didn't work. I'm getting this error "application-defined or object-defined error" in this line.. RemoveDuplicates Columns:=icol, Header:=xlYes – Hideandseek Jul 26 '15 at 03:27
  • That is why sample data is so important. I used `.Cells(1, 1).CurrentRegion` to quickly get the block of data since many people simply start at A1 and build right and down. It seems that you do not as there must be blank columns and/or rows between A1 and whatever cell contains *'abcd'*. As you are reluctant to supply sample data, please refer to [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx) for information on how you can adjust the code for your own purposes. –  Jul 26 '15 at 03:33
  • I had blank column in header. It works fine now. Thanks. – Hideandseek Jul 26 '15 at 04:49
  • Btw. Is there any way to find number of duplicates in that column.? – Hideandseek Jul 26 '15 at 05:00
  • Yes but you would have to know the starting cell. Knowing the starting and ending row as well would make it more efficient. Worksheet functions could produce a count in either VBA or on the worksheet as a standard formula. –  Jul 26 '15 at 05:05
  • Starting cell would be the header cell 'abcd'. I don't know the end cell. Assume it's for entire column. How can I check number Of duplicates ? – Hideandseek Jul 26 '15 at 05:34
5

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

Community
  • 1
  • 1
mahi_0707
  • 1,030
  • 9
  • 17
0

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

Jorge Jorge
  • 11
  • 2