VBA: How to Remove Duplicate Values

You can use the following methods to remove duplicate values in VBA:

Method 1: Remove Duplicate Values Based on One Column

Sub RemoveDuplicates()
    Range("A1:C11").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

This particular example removes duplicate rows in the range A1:C11 based on duplicate values in the first column of the range.

The argument Header:=xlYes specifies that the first row in the range is a header row and should not be considered when looking for duplicates.

Method 2: Remove Duplicate Values Based on Multiple Columns

Sub RemoveDuplicates()
    Range("A1:C11").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

This particular example removes duplicate rows in the range A1:C11 based on duplicate values in the first two columns of the range.

The following examples show how to use each of these methods in practice with the following dataset in Excel:

Example 1: Remove Duplicate Values Based on One Column

We can create the following macro to remove rows that have duplicate values in the first column:

Sub RemoveDuplicates()
    Range("A1:C11").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

When we run this macro, we receive the following output:

Notice that each row with a duplicate value in the first column of the dataset has been removed.

We can create the following macro to remove rows that have duplicate values in the first two columns:

Sub RemoveDuplicates()
    Range("A1:C11").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

When we run this macro, we receive the following output:

Notice that each row with duplicate values in the first two columns of the dataset have been removed.

Note: You can find the complete documentation for the VBA RemoveDuplicates method here.

The following tutorials explain how to perform other common tasks in VBA: