VBA to Remove Duplicates in Excel

Duplicates in Excel can cause unwanted effects, such as distorting the accuracy of data analysis and reporting. For instance, duplicates can complicate tasks such as VLOOKUP and HLOOKUP operations, leading to inaccurate results.

Removing duplicates helps you work with a clean and concise dataset, making data analysis more efficient.

Although you can remove duplicates manually or use options on the Excel user interface, VBA provides a quicker and more efficient alternative.

This tutorial shows five examples of VBA to remove duplicates in Excel. All the examples use the “RemoveDuplicates” method of the “Range” object.

Note: Creating a data backup before using any example codes in this tutorial is essential, as they modify your data directly.

Explanation of the RemoveDuplicates Method

The “RemoveDuplicates” method removes repeating values from a range of values.

The syntax of the method is shown below:

expression.RemoveDuplicates (Columns, Header)
  • expression – This is a Range object variable.        
  • Columns – This is a required parameter of variant data type and is an array of indexes of the columns with duplicate values.
  • Header – This parameter is optional and specifies whether the first row has header information. Its data type is “xlYesNoGuess.” Specify “xlYes” if the first row has header information, “xlNo” (the default option) if it doesn’t have header information, and “xlGuess” if you want Excel to determine the header.                

VBA to Remove Duplicates From One Column in Excel

The one-column dataset below has a header and contains duplicate values that have been color-coded for easier identification.

Dataset with duplicates in a column

We want to use Excel VBA code to remove the duplicate values.

We use the steps below:

  1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
  2. Click the “Insert” option on the menu bar and choose the “Module” menu item to insert a standard VBA module.
Insert a module in Excel VB editor
  1. Copy and paste the following code into the module:
Sub RemoveDuplicatesFromOneColumn()
    Range("A1:A16").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Note: You can customize the code for your situation by changing the range, columns, and header settings.

  1. Press the shortcut Ctrl + S or click the “Save” button on the toolbar and click “No” on the message box.
Click yes in the dialog box
  1. Save the workbook file as an Excel Macro-Enabled Workbook.
Save the file
  1. Press Alt + F11 to switch to the active worksheet containing the dataset.
  2. Press Alt + F8 to open the “Macro” dialog box, select the “RemoveDuplicatesFromOneColumn” macro on the “Macro name” list box, and click “Run.”
Select and run the macro

The code executes and removes duplicate values from the dataset, as shown below:

Duplicates from column have been removed
Also read: Remove Blank Rows in Excel (VBA)

VBA to Remove Duplicates From Multiple Columns in Excel

The Excel dataset below has a header and contains duplicate values in columns A and C, which have been color-coded for easier identification.

duplicates in multiple columns

We want to use VBA code to delete duplicate values from columns A and C.

We use the steps below:

  1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
  2. Click the “Insert” option on the menu bar and choose the “Module” menu item to insert a standard VBA module.
  3. Copy and paste the following code into the module:
Sub RemoveDuplicatesFromManyColumns()
  Range("A1:D10").RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub
  1. Save the workbook file as an Excel Macro-Enabled Workbook, as shown in Example #1.
  2. Press the shortcut Alt + F11 to switch to the active worksheet containing the dataset.
  3. Press the shortcut Alt + F8 to open the “Macro” dialog box, select the “RemoveDuplicatesFromManyColumns” macro on the “Macro name” list box, and click “Run.”
Run the macro To remove duplicate from multiple columns

The code executes and removes duplicate values from columns A and C, as shown below:

Duplicate from multiple columns removed

Note: The code deletes entire rows with duplicate values.

Also read: VBA Macro Codes to Filter Data In Excel

VBA to Remove Duplicates From a Used Range in Excel

In previous examples, we specified the range to remove duplicates in the code. Here, we show how to use the “UsedRange” property of the “ActiveSheet” object.

Note: The “UsedRange” property of the “ActiveSheet” object returns a “Range” object representing the used range on the specified worksheet. The used range is the cell range that has contained a value at any time.

The Excel dataset below is the used range on the active worksheet, and it contains duplicate values in columns A and C, which have been color-coded for easier identification.

used range with duplicates

We want to use VBA code to remove duplicates from columns A and C of the used range.

We use the following steps:

  1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
  2. Click the “Insert” option on the menu bar and choose the “Module” menu item to insert a standard VBA module.
  3. Copy and paste the following code into the module:
Sub RemoveDuplicatesFromUsedRange()
 ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub
  1. Save the workbook file as an Excel Macro-Enabled Workbook, as shown in Example #1.
  2. Press Alt + F11 to switch to the active worksheet containing the dataset.
  3. Press Alt + F8 to open the “Macro” dialog box, select the “RemoveDuplicatesFromManyColumns” macro on the “Macro name” list box, and click “Run.”
Execute the macro

The code runs and deletes duplicates from columns A and C of the used range, as shown below:

Duplicates removed from the used range

Note: The code deletes entire rows containing duplicate values.

Also read: Delete Blank Rows Using VBA

VBA to Remove Duplicates From an Excel Table

The previous examples have shown VBA code to remove duplicates from normal cell ranges.

This example shows VBA code to remove duplicates from an Excel table.

Note: Excel tables are dynamic ranges of organized and pre-formatted cells. They have features like automatic updates, data aggregation, and data styling. In contrast, normal cell ranges are a fixed group of cells that lack these capabilities.

The following Excel table has duplicate values in columns A and B. The values are color-coded for easier identification.

Excel table with duplicates

We want to use VBA code to remove the duplicate values in columns A and B.

We use the below steps:

  1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
  2. Click the “Insert” option on the menu bar and choose the “Module” menu item to insert a standard VBA module.
  3. Copy and paste the following code into the module:
Sub RemoveDuplicatesFromTable()
    ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub

Note: The code retrieves the table on the current worksheet using the “ListObjects” property of the “ActiveSheet” object. Afterward, using the Excel table’s “DataBodyRange” property, the code returns the range of cells containing the actual data while excluding headers and totals. Any duplicates in the specified columns are then removed.  

  1. Save the workbook as an Excel Macro-Enabled Workbook, as shown in Example #1.
  2. Press Alt + F11 to switch to the active worksheet containing the Excel table.
  3. Press Alt + F8 to open the “Macro” dialog box, select the “RemoveDuplicatesFromTable” macro on the “Macro name” list box, and click “Run.”
Run macro to remove duplicates from table

The VBA code executes and removes duplicates from columns A and C of the Excel table, as shown below:

Duplicates removed from Excel table
Also read: VBA to Delete Rows Based On Cell Value

VBA to Remove Duplicates From a Horizontal Range

The previous examples have described VBA to remove duplicates from vertical ranges.

This example shows how to remove duplicates from a horizontal range.

The following horizontal dataset has row headers in column A and duplicate values in rows 1, 2, and 3. The dataset is on a worksheet called “Horizontal Range.”

Duplicates in horizontal range

We want to use the VBA code to remove the duplicate values in rows 1, 2, and 3.

We use the following steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Click the “Insert” option on the menu bar and choose the “Module” menu item to insert a standard VBA module.
  3. Copy and paste the following code into the module:
Sub RemoveDuplicatesFromHorizontalRange()

    'Add a new worksheet after the active worksheet
    Sheets.Add After:=ActiveSheet
    
    'Name the new worksheet 'NewWorkSheet'
    ActiveSheet.Name = "NewWorkSheet"
    
    'Copy the data from the original worksheet
    Sheets("Horizontal Range").UsedRange.Copy
    
    'Activate the new worksheet
    Sheets("NewWorkSheet").Activate
    
    'Paste and transpose the data to arrange it in columns
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
   'Remove the duplicate values from the pasted data
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    
    'Clear all the data from the original worksheet
    Sheets("Horizontal Range").UsedRange.ClearContents
    
    'Copy the columns of data from the new worksheet
    Sheets("NewWorkSheet").UsedRange.Copy
    
   'Activate the original worksheet
    Sheets("Horizontal Range").Activate
    
   'Paste and transpose the data without duplicates
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    'Delete the worksheet that was added because it is no longer required
    Sheets("NewWorkSheet").Delete
    'Activate the original worksheet
    
    Sheets("Horizontal Range").Activate
   
End Sub

Note: I have added comments at each step to make it easy for you to follow.

  1. Save the workbook file as an Excel Macro-Enabled Workbook, as shown in Example #1.
  2. Press the shortcut Alt + F11 to switch to the active worksheet containing the Excel table.
  3. Press the shortcut Alt + F8 to open the “Macro” dialog box, select the “RemoveDuplicatesFromHorizontalRange” macro on the “Macro name” list box, and click “Run.”
Run macro to remove duplicates from horizontal range
  1. Confirm deletion of the new worksheet by pressing Enter or clicking “Delete” on the warning message box.
Confirm deletion of sheet that was added

The code runs and removes duplicate values from the horizontal dataset, as shown below:

Horizontal data without duplicates

This tutorial presented five examples of VBA to remove duplicates in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment