Excel VBA: Remove Duplicates Comparing Multiple Columns: 3 Methods

Method 1 – Remove Duplicates Comparing Specified Multiple Columns Using VBA in Excel

Task: We want to remove duplicate students from the dataset by comparing the columns- Student Id and Student Name.

Problem Analysis: From the dataset analysis, we see that based on 1st two columns there are two duplicates- John and Alex. In addition, we have the name John 4 times but the last appearance has a different student id. The last appearance of the name John is a duplicate based on only the 2nd column, which is unique comparing both the 1st and 2nd columns. The last appearance should not be removed in this case.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Sub RemoveDuplicatesMultipleColumns()
Dim dataRange As Range
Set dataRange = Range("B4:F15")
dataRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

Code Explanation: In the above code, we used the Range object to select the whole dataset B4:C15. As the dataset has a header, we defined the 2nd argument as xlYes.

Output: The above code successfully removed the duplicate values.

Method 2 – Run a VBA Code to Remove Duplicates Comparing All Columns in a Dataset

Task: We want to remove duplicate students from the dataset by comparing all columns.

Problem Analysis: From the dataset analysis, we see that based on 1st two columns, there are two duplicates-John and Alex. See that marks in row 12 differ from data for John in rows 6 and 9. The last appearance should not be removed in this case.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Option Explicit
Sub RemoveDuplicatesMultipleColumns()
Dim dataRange As Range
Dim colNum As Variant
Dim i As Integer
Set dataRange = [B4].CurrentRegion
ReDim colNum(0 To dataRange.Columns.Count - 1)
For i = 0 To UBound(colNum)
colNum(i) = i + 1
Next i
dataRange.RemoveDuplicates Columns:=(colNum), Header:=xlYes
End Sub

Output: The above code successfully removed the duplicate values.

Method 3 – Remove Duplicates from Selected Range Comparing Multiple Columns in Excel VBA

Task: We want to remove duplicate students from the selected portion of the dataset by comparing specific or all columns.

Solution: Use the Application.Selection property to select the portion of the dataset from where duplicates will be removed.

Problem Analysis: Our code will not remove duplicates at rows 11 and 12. These two duplicates are out of working range in this case.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Option Explicit
Sub RemoveDuplicatesMultipleColumns()
Dim dataRange As Range
Dim colNum As Variant
Dim i As Integer
Set dataRange = Selection
ReDim colNum(0 To dataRange.Columns.Count - 1)
For i = 0 To UBound(colNum)
colNum(i) = i + 1
Next i
dataRange.RemoveDuplicates Columns:=(colNum), Header:=xlYes
End Sub

Output: The above code removed the only duplicate value at row 9.