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

User · Answer

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

User · Answer

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       lt -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

User · Answer

If you got only one column in the range to clean  just add  quot  1  quot  to the end  It indicates in wich column of the range Excel will remove the duplicates  Something like   Sub norepeat        Range  quot C8 C16 quot   RemoveDuplicates  1   End Sub  Regards

[vba] Remove Duplicates from range of cells in excel vba

Examples related to vba

Examples related to excel

Examples related to excel-2013