How to Use VBA to Remove Duplicates in Excel - 6 Examples

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 duplicate values
Header	Optional	xlYes xlNo xlGuess	Specifies whether the range has headers. xlYes: if the range contains headers xlNo: Default value; If the range doesn’t contain headers. xlGuess: determines the header.

Parameter

Required/ Optional

Data Type

Description

Columns

Required

Variant

The range of indexes of the columns that contain duplicate values

Header

Optional

xlYes
xlNo
xlGuess

Specifies whether the range has headers.

xlYes: if the range contains headers
xlNo: Default value; If the range doesn’t contain headers.
xlGuess: determines the header.

Method 1- Using VBA to Remove Duplicates from a Single Column in Excel

Steps:

Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.

Click Insert -> Module.

Copy the following code into the code window.

Sub RemoveDuplicatesFromSingleCol()
    Range("B5:B15").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Press F5 or select Run -> Run Sub/UserForm. You can also click the small Play icon.

This is the output.

Method 2 – Applying a VBA Macro to Remove Duplicates from Multiple Columns

Steps:

Go to Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub RemoveDuplicatesFromMultiCol()
  Dim Rng As Range
  Set Rng = Range("B5:D15")
  Rng.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub

This is the output.

Method 3 – Creating a Macro to Delete Duplicates from a User-Specified Range in Excel

Steps:

Go to Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub RemoveDuplicatesFromUserRng()
 ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

This is the output.

Method 4 – Using a Macro to Remove Duplicate Rows

Steps:

Go to Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub RemoveDuplicateRows()
  Range("B5:C15").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

This is the output.

Method 5 – Applying a VBA Macro to Eliminate Duplicate Rows

Steps:

Go to Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub RemoveDuplicateRowsFromTable()
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

This is the output.

Method 6 – Using Excel VBA to Remove Duplicates from Rows

Steps:

Go to Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

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