Sometimes you want to remove duplicate values from your worksheet to work only with the unique values. In Excel, you can remove duplicates very easily and in this article, we will show you how to do that with VBA macro.
VBA Syntax to Remove Duplicates in Excel
- Generic Syntax
Range.RemoveDuplicates(Columns, Header)
- Parameter Description
| Parameter | Required/ Optional | Data Type | Description |
|---|---|---|---|
| Columns | Required | Variant | The range of indexes of the columns that contain the duplicate values |
| Header | Optional |
|
Specifies whether the range has headers or not.
|
In this section, you will know how to delete duplicate values from a single column, from multiple columns, from a user-specified range, how to remove duplicate rows from an Excel sheet, duplicate rows from a table and duplicate values from rows of data in Excel with VBA code.
1. Using VBA to Remove Duplicates from a Single Column in Excel
Look at the following dataset of a single column that contains duplicate values.
With VBA code we will know how to remove the duplicate values present in the column.
Steps:
- Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- In the pop-up code window, from the menu bar, click Insert -> Module.
- Copy the following code and paste it into the code window.
Sub RemoveDuplicatesFromSingleCol()
Range("B5:B15").RemoveDuplicates Columns:=1, Header:=xlYes
End SubYour code is now ready to run.
- Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.
You will notice that the duplicate values from the given column have been removed by running the VBA code.
2. Applying VBA Macro to Remove Duplicates from Multiple Columns
From the previous section, we have learnt how to remove duplicate values from a single column, but here we will know how to do that when there are multiple columns.
Look at the above dataset, it has two columns with repetitive values. With VBA code we will know how to delete those in Excel.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub RemoveDuplicatesFromMultiCol()
Dim Rng As Range
Set Rng = Range("B5:D15")
Rng.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End SubYour code is now ready to run.
Notice in the following picture that we have successfully deleted the duplicate values from multiple columns in Excel.
3. Creating Macro to Delete Duplicates from User-Specified Range in Excel
Till now we have been specifying the range inside our code when removing duplicates. But here you will learn the VBA code to delete duplicates in Excel when the user is specifying the range.
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub RemoveDuplicatesFromUserRng()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End SubYour code is now ready to run.
This piece of code will automatically remove the duplicate values selected by users.
4. Using Macro to Erase Duplicate Rows from Excel
We have been working with columns till now. In this phase, we will learn how to remove duplicate rows from Excel with VBA.
We will delete the whole rows from the above dataset which contain duplicate values.
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub RemoveDuplicateRows()
Range("B5:C15").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End SubYour code is now ready to run.
Look at the result below, the whole rows containing duplicate values are removed.
5. Applying VBA Macro to Eliminate Duplicate Rows from Excel Table
Here, we will show you how to eliminate duplicate rows from a table in Excel.
Above is the table containing duplicate rows. With VBA code we will show you how to erase those.
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub RemoveDuplicateRowsFromTable()
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End SubYour code is now ready to run.
With this piece of VBA code, you will easily get your Excel table without any duplicate rows in it
6. Using Excel VBA to Remove Duplicates from Rows of Data
You can also remove duplicate values from rows of data in Excel with some “out of the box” thinking with the VBA code.
Look at the following example. We will remove the duplicate values from these rows of data with VBA macro in Excel.
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Sub RemoveDuplicatesFromRowsOfData()
'Add a new worksheet
Sheets.Add After:=ActiveSheet
'Call the new worksheet as 'NewSheet'
ActiveSheet.Name = "NewSheet"
'Copy the data from the original worksheet
Sheets("Rows of Data").UsedRange.Copy
'Activate the new sheet
Sheets("NewSheet").Activate
'Paste transpose the data to place it in columns
ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'Remove the duplicates
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
'Clear the data from the original worksheet
Sheets("Rows of Data").UsedRange.ClearContents
'Copy the columns of data from the new worksheet
Sheets("NewSheet").UsedRange.Copy
'Activate the original sheet
Sheets("Rows of Data").Activate
'Paste transpose the non-duplicated data
ActiveSheet.Range("B2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'Delete the copy sheet (no longer needed)
Sheets("NewSheet").Delete
'Activate the original sheet
Sheets("Rows of Data").Activate
End SubYour code is now ready to run.
You will get the rows of data without any duplicate values by running the provided VBA code.
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
This article showed you how to remove duplicates in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
