VBA code to remove duplicate Rows in Excel: Example Macros to delete duplicate records from worksheet in MS Excel 2003, 2007, 2010, 2013. Example to show you how to delete duplicate records from Excel Worksheet. We will also see the example for deleting the duplicates for the data with and without column headers.

VBA Remove duplicates in Excel Range

Remove Duplicates function is introduced form MS Excel 2007, You can use RemoveDuplicates method to remove the duplicate records in Excel 2007 and higher. If you are using Excel 2003, you can use different approaches to remove the duplicates, see the last example for removing duplicates in Excel 2003.

VBA code to Remove Duplicate Rows

Here is the Example VBA syntax and Example VBA Macro code to Remove Duplicate Rows in excel worksheets. This will help you to know how to delete duplicate records from Excel workbook using VBA.

VBA Remove Duplicate Rows: Syntax

Following is the VBA Syntax and sample VBA macro command to delete duplicate rows from worksheet using VBA. We are using the RemoveDuplicates method of the Cells object of worksheet.


Cells.RemoveDuplicates Columns:=Array(

[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
120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: VBATags:

Share This Story, Choose Your Platform!

8 Comments

  1. Arafin April 24, 2016 at 5:06 PM

    Hi
    If I have ‘n’ number of duplicate for a record and i want to keep only the last record (last record will be always at the end as per my source data file). My first identifier should be the Column B and second identifier is Column F. And to be precise I want to remove the duplicate values where both B and F matches. If B and F do not match then its not duplicate row.
    Appreciate if you can help me.

    Regards
    Arafin

  2. PNRao September 14, 2016 at 2:27 PM

    Remove Duplicates based on Multiple Columns:
    Here is the VBA code to Remove Duplicate based on Multiple Columns:

    Sub sbRemoveDuplicates_BasedOnMultipleColumns()
    Cells.RemoveDuplicates Columns:=Array(1,2)
    'Here 1 and 2 are Column Numbers
    End Sub
    

    Remove Duplicates based on Multiple Columns: Keep The Last Occurance

    And the below procedure will removes duplicates and keep the Last Occurrence / Record.

    Sub RemoveDuplicatesBasedOnTwoColumns_KeepLastRecord()
    
    
    Col1 = 2 'B
    Col2 = 6 'F
    
    'Now Find Last Row - Assuming you will have maximum of 100000
    If Cells(100000, Col1).End(xlUp).Row > Cells(100000, Col2).End(xlUp).Row Then
        lastRow = Cells(100000, Col1).End(xlUp).Row
    Else
        lastRow = Cells(100000, Col2).End(xlUp).Row
    End If
    
    
    'Now Delete the Duplicate Records
    skipCntr = 0
    For iCntr = 1 To lastRow
    If Application.WorksheetFunction.CountIfs(Columns(Col1), Cells(iCntr, Col1), _
    Columns(Col2), Cells(iCntr, Col2)) > 1 Then
        Rows(iCntr).Delete
        iCntr = iCntr - 1
        skipCntr = skipCntr + 1
    End If
    If iCntr + skipCntr >= lastRow Then Exit For
    Next
    
    
    End Sub
    

    Thanks!

  3. Arnoud October 20, 2016 at 9:51 PM

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

    this really worked for me, THANKS

  4. Valerie October 13, 2017 at 8:01 PM

    Hi
    I have the same issue but with column A and B instead of B and F. Have you ever gotten an answer?

  5. PNRao October 14, 2017 at 2:23 PM

    The below Macro will find the duplicates based on the two columns and remove it, except the last occurrence.

    Sub RemoveDuplicatesBasedOnTwoColumns_KeepLastRecord()
    
    
    Col1 = 1 'A
    Col2 = 2 'B
    
    'Now Find Last Row - Assuming you will have maximum of 100000
    If Cells(100000, Col1).End(xlUp).Row > Cells(100000, Col2).End(xlUp).Row Then
        lastRow = Cells(100000, Col1).End(xlUp).Row
    Else
        lastRow = Cells(100000, Col2).End(xlUp).Row
    End If
    
    
    'Now Delete the Duplicate Records
    skipCntr = 0
    For iCntr = 1 To lastRow
    If Application.WorksheetFunction.CountIfs(Columns(Col1), Cells(iCntr, Col1), _
     Columns(Col2), Cells(iCntr, Col2)) > 1 Then
        Rows(iCntr).Delete
        iCntr = iCntr - 1
        skipCntr = skipCntr + 1
    End If
    If iCntr + skipCntr >= lastRow Then Exit For
    Next
    
    End Sub
    

    Thanks!

  6. Erika February 14, 2018 at 2:39 PM

    Hi hope you can help me with this too. I read forums already but did not seem to find an answer specific to my need.I have set of data in sheet1, and I need to identify duplicate values using column name”part” only. Then, I need to transfer the duplicates to sheet2 (retain only 1 unique value in sheet1). The criteria of which of the duplicates will be transferred to sheet2, is column “count”. duplicate values in column “part” must be 0 in column “count”, for the row to be transferred to sheet2. by the way, header is in row1, and the goal is to transfer duplicate “part” but 0 in “count” to sheet2. Thanks a lot.

  7. hari September 23, 2020 at 9:11 AM

    Hi ,

    I have duplicate values in column D under multiple sheets, how this can be deleted with vba . No need to match the data among the sheets. It just have to find the duplicates in column D and delete the row from all sheets in the work book

  8. hari September 23, 2020 at 9:12 AM

    Hi ,

    Hope you can help me on this, I have duplicate values in column D under multiple sheets, how this can be deleted with vba . No need to match the data among the sheets. It just have to find the duplicates in column D and delete the row from all sheets in the work book.

Leave A Comment