Table of Contents
Using VBA to remove duplicates in Excel is a fairly simple process. The first step is to open the VBA editor, then create a macro that will loop through the specified range and identify duplicate values. The macro will then delete the duplicate row or column and move on to the next value. Lastly, the macro can be executed either manually or by using a shortcut, making the process even faster.
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.
Example 2: Remove Duplicate Values Based on Multiple 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 .