VBA to Remove Duplicate Rows in Excel

Home/VBA/VBA to Remove Duplicate Rows in Excel

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
LIMITED TIME OFFER
By |January 19th, 2015|VBA|2 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

2 Comments

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

    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. Arnoud October 20, 2016 at 9:51 PM - Reply

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

    this really worked for me, THANKS

Leave A Comment


Related pages


gantt project planner excel templatecolor formula in excel 2007vb6 file existsms excel test questions and answershow to create bar and line chart in excel 2010unhiding columns in excelhow to password protect excel file 2010how to run a macro in excel 2007excel template dashboardvba code msgboxunlock microsoft excelvba code to read excel fileexcel lookup table tutorialexcel proposal templateexport vbahlookup function in excel 2010excel formula offsetvlookup in excel 2010 with examplevlookup codehow to generate charts in excelexcel vba book pdfexcel remove workbook protectionexcel match function exampleimport worksheet excelsumif function excel 2010planning calendar template exceladvanced macros in excelhow to use the choose function in excelsas macros for beginnersvba import data from another workbookadd clearing choice buttonhow do you delete rows in excelvba in excel tutorialcombining worksheets in excelinsert blank rows in excelvba for access tutorialiserrorhow to delete multiple cells in excelconvert excel rows to columnshow to put numbers in ascending order in excelvb script excelsql pivot table tutorialsumif excelldbms commands with examples pdfadodb.connection connection stringhow to use pivot in excel 2007connect excel to oracle databaseexcel trim functionexcel dynamic graphpie chart template excelsample excel spreadsheet for practicelookup excel vbavba multiple if statementsmacro learning in excelexcel template plannerfollow hyperlink vba excelhow to make a pivot table in excel 2013how do you delete columns in excelif elseif vbaworkbooks.activatevba activate cellcase syntax vbavba active worksheetload userformdelete duplicate cells excelvlookup error refpivot table tutorial excel 2013using formulas in vbaproject portfolio dashboard excelvba excel worksheetsadding columns in excelexcel vba set activecellvb6 with access database exampleunprotect worksheet excel 2013unlock protected workbookvbscript examples programsunmerge cells in excel 2010project charter template excel