Excel VBA Remove Duplicates Values

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.