Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 3 examples to remove duplicates by comparing multiple columns using VBA in Excel. Here we’ll use Excel’s built-in Range.RemoveDuplicates method to configure our code. Let’s dive into the examples to learn more.

Table of Contents hide

Download Practice Workbook

3 Examples to Remove Duplicates Comparing Multiple Columns Using VBA in Excel

1. Remove Duplicates Comparing Specified Multiple Columns Using VBA in Excel

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

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

Notes

Conclusion

Download Practice Workbook

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

Remove Duplicates Multiple Column.xlsm

3 Examples to Remove Duplicates Comparing Multiple Columns Using VBA in Excel

Dataset Analysis: To illustrate different examples, we’ll use the following sample dataset with duplicate entities. We’ll show examples of how to detect and remove duplicates by comparing multiple columns using VBA code.

In the dataset, we see that-

The student name John appears 4 times; 3 times for student id 1012 and once for 1018.
Jhon with id 1012, has the same marks in rows 6 and
The student name John with ids 1012 in row 12 has different marks from rows 6 and 9..
In rows 5 and 10, student Alex appears twice with the same student id, mark and others

Introduction to the Range.RemoveDuplicates Method

Excel’s Range.RemoveDuplicates method is used to remove duplicates from a range of values. The syntax of this method is-

expression.RemoveDuplicates(Columns, Header)

Here, expression is a variable that represents a Range object i.e., a row, a column, or a selection of cells.
Columns– The array of indexes that represents column numbers from where we want to remove duplicates.
Header– To determine whether the selected range has a header or not. It has 3 possible values-
(i) xlYes– The selected range has a header.
(ii) xlNo– The selected range has no header.
(iii) xlGuess– Let Excel determine whether there is a header or not.

Write Code in Visual Basic Editor

To remove duplicates from multiple columns, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

Go to the Developer tab from the Excel Ribbon.
Click the Visual Basic option.

In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

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. So, the last appearance of the name John is a duplicate based on only the 2nd column that is unique comparing both the 1st and 2nd column. As a result, 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.

Similar Readings

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. In addition to the previous example, we see that marks in row 12 differ from data for John in rows 6 and 9. As a result, 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.

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: We just need to use the Application.Selection property to select our desired portion of the dataset from where duplicates will be removed.

Problem Analysis: As we only selected a portion of the dataset, 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 successfully 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")

Conclusion

Now, we know how to remove duplicates by comparing multiple columns using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.