Excel VBA – Remove Duplicates from Data Range
To remove duplicate values from a data range or column, this VBA code is going to use the build in option ‘Removeduplicates’.
You can choose to remove duplicates from a single column or multiple columns, can have headers or not.
All these scenarios are covered in this below VBA code.
Sub Excel_VBA_Remove_Duplicates() 'Option 1: Using RemoveDuplicates Function in VBA 'Single Column + With No Header Row ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo 'Combining Multiple Column Data + With Header Row ActiveSheet.Range("$A$1:$C$20").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes End Sub
This code is a direct macro conversion of this topic.
Excel VBA Delete Duplicates using Advanced Filter
As explained in that topic, we can also remove duplicates and get only the unique values using advanced autofilter option.
The same can be applied in VBA code as well.
Here is how it is done.
Sub Excel_VBA_Remove_Duplicates_Advanced_Filter() 'Option 2: Using Advanced Filter in VBA to Remove Duplicate values Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True End Sub
The difference between the two options is that, in the first option the values are overwritten.
But in the second method, we choose to write the output to a different location. So, the original values are preserved and we can verify what values went missing.
In case, if you dont want to remove duplicate entries, but just want to highlight the duplicate values, then you can follow this link.