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.
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.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. 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. End Sub
Cells.RemoveDuplicates Columns:=Array(
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.
Explained VBA Code to Delete Duplicate RowsStarting Macro program and sub procedure to write VBA code to delete duplicate records in worksheet.
Sub sbRemoveDuplicates()
Cells.RemoveDuplicates Columns:=Array(1)
End statement to end the VBA code to remove duplicate records in ExcelVBA 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
Premium Project Management Templates
50+ Professional Project Management Templates!
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.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates
8 Comments