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

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 3 examples of removing 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.


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

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.
  • John 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.
  • 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.

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

Read More: How to Remove Duplicate Rows Based on One Column Using Excel VBA


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.


Conclusion

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


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo