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

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.


Download Practice Workbook

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


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 columnsStudent 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.

Excel VBA Remove Duplicates Multiple Columns

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.

Excel VBA Remove Duplicates Multiple Columns


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.

Excel VBA Remove Duplicates Multiple Columns

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.

Excel VBA Remove Duplicates Multiple Columns


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.

Excel VBA Remove Duplicates Multiple Columns

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.

Excel VBA Remove Duplicates Multiple Columns


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.

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo