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.

Notes

We could use the Worksheet.UsedRange property to select the whole dataset. In this case, the line of code is-

Set dataRange = UsedRange

Instead of,

Set dataRange = Range("B4:F15")

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• Excel VBA: Remove Duplicates from an Array
• How to Delete Duplicate Rows in Excel with VBA