VBA to Remove Duplicate Rows in Excel

[Column Numbers])


Here Cells.Remove Duplicates command tells excel to remove the duplicated based on the specified list of column array. Columns:=Array([Column Numbers]) will help us to specify the list of columns to combine and treat it as a duplicate record.

VBA Remove Duplicate Rows: Examples

The following Excel VBA macro code is to delete duplicate rows from the worksheet. This VBA macro will delete the records based on the column 1.

Sub sbRemoveDuplicates()
    Cells.RemoveDuplicates Columns:=Array(1)
End Sub 

Instructions to run the VBA Macro code to delete duplicate rows in Excel

Please follow the below steps to execute the VBA code to delete duplicate Rows in Excel.

• Step 1: Open any Excel workbook
• Step 2: Press Alt+F11 – This will open the VBA Editor
• Step 3: Insert a code module from then insert menu
• Step 4: Copy the above code and paste in the code module which have inserted in the above step
• Step 5: Enter some data in first column of worksheet. Also enter some duplicate data for testing purpose
• Step 6: Now press F5 to execute the code

Explained VBA Code to Delete Duplicate RowsStarting Macro program and sub procedure to write VBA code to delete duplicate records in worksheet.
Sub sbRemoveDuplicates()

Here Cell refer all entire worksheet. RemoveDuplicate method is for removing the duplicate records. And the Columns parameter is to specify the based on which column to remove duplicate records.
Cells.RemoveDuplicates Columns:=Array(1)

End Sub
End statement to end the VBA code to remove duplicate records in Excel

VBA Remove Duplicate Rows: With Header Example and Syntax

Here is the Example VBA Code to delete duplicates of the data with headers.

'VBA code to remove duplicates from data with headers
Sub sbRemoveDuplicatesSpecificWithHeaders()
Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlYes 
End Sub

VBA Remove Duplicate Rows: Without Header Example and Syntax

Here is the Example VBA Code to delete duplicates of the data without headers.

‘Starting procedure to write VBA code to remove duplicates from data with no headers
Sub sbRemoveDuplicatesSpecificWithNoHeaders()
Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlNo
End Sub

What if you are still using Excel 2003?

You can use different methods to remove the duplicate records, the best way is Advanced filters (I will write a detailed article). Or you can write an User Defined Function using VBA.
The below macro will use advanced filter function in the Excel to Remove the Duplicate data.

Sub sb2003RemoveDuplicatesFromSpecificRange()

Dim myRng, myDuplicateRange As Range
Set myRng = Range("A2:B12")
myRng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'The above statement will hide all duplicate rows in the given range

'Now we have remove the the hidden rows and leave the visible rows (unique rows)
Set myDuplicateRange = Nothing
For Each Row In myRng.Rows
        If Row.EntireRow.Hidden Then
            If myDuplicateRange Is Nothing Then
                Set myDuplicateRange = Row
            Else
                Set myDuplicateRange = Union(myDuplicateRange, Row)
            End If
        End If
    Next

    If Not myDuplicateRange Is Nothing Then myDuplicateRange.EntireRow.Delete

'Now clear the advanced filters and show all records
    ActiveSheet.ShowAllData

'In other way, You can copy all visible rows (Unique rows) into the another sheet, Instead of removing the duplicates
End Sub