Rifat Hassan
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.
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.
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).
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.
Step 2:
➤ Come back to your worksheet.
➤ Select your data set and run this macro.
➤ It will execute the same thing as did above. Remove the rows with duplicates in column 1 (Student Name).
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.
Step 2:
➤ Come back to your worksheet.
➤ Select your data set and run this macro.
➤ This time it will remove the rows only if both the name and the student ID are the same.
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)
