How to Remove Duplicates in Excel Using VBA (3 Quick Methods)

One of the most important activities in Excel is to remove duplicates from a given data set. Today I’ll show how to remove duplicates in Excel using VBA only.


Download Practice Workbook


3 Quick Methods to Use VBA in Excel to Remove Duplicates

Here we’ve got a data set with the Names, IDs, Marks, and Grades in the examination of some students of a school called Sunflower Kindergarten.

Data Set to Remove Duplicates in Excel Using VBA

If you look carefully, you will find that some names have been repeated mistakenly.

Today our objective is to remove the duplicate values using Excel VBA.


1. Use VBA to Remove Duplicates from Fixed Cell Range

First of all, we will try to remove the duplicate names using a fixed cell range in the VBA code.

Here, our data set is the range B3:E15 in the workbook (Including the Column Headers).

We will use this fixed cell range in the code here.

Step 1:

➤ Open a new VBA window and insert a new module (Click here to see how to open and insert a new VBA module in Excel).

➤ Insert this code in the module:

Code:

Sub Remove_Duplicates()

Range("A3:E14").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

➤ It produces a Macro called Remove_Duplicates. A3:E14 is the range of my data set, and I want to remove the duplicate rows based on Column 1. You use your one.

VBA Code with Fixed Cell Reference to Remove Duplicates

Step 2:

➤ Come back to your worksheet and run this Macro (Click here to see how to run Macro).

➤ It will remove the rows with duplicates in column 1 (Student Name).

Remove Duplicates with VBA Code with Fixed Range


2. Insert VBA Codes to Remove Duplicates from Selected Cell Range

Now we will try to build a macro that can remove duplicates from any selected cell range in the worksheet.

Step 1:

➤ Open a new VBA window again and insert another new module.

➤ Insert this code in the module:

Code:

Sub Remove_Duplicates()

Dim Rng As Range

Set Rng = Selection

Rng.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

➤ It produces a Macro called Remove_Duplicates. I want to remove the duplicate rows based on Column 1. You use your one.

VBA Code to Remove Duplicates with Selected Range

Step 2:

➤ Come back to your worksheet.

➤ Select your data set and run this macro.

Selecting the Data Set

➤ It will execute the same thing as did above. Remove the rows with duplicates in column 1 (Student Name).

Remove Duplicates from Data Set Using VBA


3. Embed a VBA Macro to Remove Duplicates from Multiple Columns

Up till now we have removed the rows that have duplicates in Column 1 (Student Name).

But in fact, the names of two students can be the same, it doesn’t always mean that it has been added mistakenly if two names are the same.

But if the IDs of two students are also same, then they are the same student. Then the row is to be removed.

This time we will develop a Macro that will remove the row if both the name and ID of two rows are the same.

Step 1:

➤ Open a new VBA window again and insert another new module.

➤ Insert this code in the module:

Code:

Sub Remove_Duplicates()

Dim Rng As Range

Set Rng = Selection

Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

End Sub

➤ It produces a Macro called Remove_Duplicates. I want to remove the duplicate rows based on Column 1 and 2 (Name and ID). You use your one.

VBA Code to Remove Duplicates with Multiple Columns

Step 2:

➤ Come back to your worksheet.

➤ Select your data set and run this macro.

Remove Duplicates from Data Set Using VBA

➤ This time it will remove the rows only if both the name and the student ID are the same.

Remove Duplicates from Multiple Columns Using VBA

Note: Here it has not removed Jennifer Marlo because the IDs of the two students are different, that is, they are two different students.


Conclusion

Using these methods, you can remove duplicates from a data set in Excel using VBA. Do you have any problems? Feel free to ask us.


Related Readings

Excel Formula to Automatically Remove Duplicates (3 Quick Methods)

How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)

How to Remove Duplicate Rows in Excel (3 Ways)

How to Remove Duplicate Rows in Excel Table

How to Find, Highlight & Remove Duplicates in Excel

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo