How to Use VBA to Remove Duplicates in Excel (6 Examples)

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

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 Sub

Your 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 Sub

Your 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 Sub

Your 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 Sub

Your 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 Sub

Your 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 Sub

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

Further Readings

• How to Find Duplicates in Excel Workbook
• Merge Duplicates in Excel
• How to Count Duplicates in Excel
• How to Group Duplicates in Excel
• Hide Duplicates in Excel