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.


Download Workbook

You can download the free practice Excel workbook from here.


VBA Syntax to Remove Duplicates in Excel

  • Generic Syntax

Range.RemoveDuplicates(Columns, Header)

Syntax of remove duplicates in Excel

  • 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
  • xlYes
  • xlNo
  • xlGuess
Specifies whether the range has headers or not.
  • xlYes: If the range contains headers then select this.
  • xlNo: Default value; If the range doesn’t contain a header then select this.
  • xlGuess: This allows Excel to determine the header.

6 Examples to Remove Duplicates in Excel with VBA

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

VBA to Remove Duplicates from a Single Column in Excel

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

Result of VBA to Remove Duplicates from a Single Column in Excel


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

VBA Macro to Remove Duplicates from Multiple Columns in Excel

Notice in the following picture that we have successfully deleted the duplicate values from multiple columns in Excel.

Result of VBA Macro to Remove Duplicates from Multiple Columns in Excel


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

VBA Macro to Remove Duplicates from User-Specified Range in Excel

This piece of code will automatically remove the duplicate values selected by users.

Result of VBA Macro to Remove Duplicates from User-Specified Range in Excel


Similar Readings:


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

VBA Macro to Remove Duplicate Rows from Excel

Look at the result below, the whole rows containing duplicate values are removed.

Result of VBA Macro to Remove Duplicate Rows from Excel


5. VBA Macro to Eliminate Duplicate Rows from a 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.

VBA Macro to Remove Duplicate Rows from a Table in Excel

With this piece of VBA code, you will easily get your Excel table without any duplicate rows in it

Result of VBA Macro to Remove Duplicate Rows from a Table in Excel


6. VBA to Remove Duplicates from Rows of Data in Excel

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.

VBA to Remove Duplicates from Rows of Data in Excel

You will get the rows of data without any duplicate values by running the provided VBA code.

Result of VBA to Remove Duplicates from Rows of Data in Excel


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo