VBA Code to Remove Duplicate Rows
VBA code to Remove Duplicate Rows
Working with huge data is always exciting and challenging. From the 2007 version onward, Excel is supporting more than a million rows in each worksheet.
One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates.
In this article, we will be sharing 4 ways to delete duplicate records from your data.
In the first method, let’s make use of Excel’s inbuilt functionality of removing duplicates.
- Step 1: Select the data in your Excel sheet from which you want to remove duplicates.
- Step 2: Go to the Data ribbon and click on Remove
- Step 3: Select the columns on basis of which duplicates needs to be identified and removed. Once done, click on OK button
- Step 4: Based on the columns selected, Excel will identify and delete the duplicate records where all selected columns have same values. You will receive confirmation about the number of records deleted by Excel
- Step 5: Based on the columns selected, Excel will identify and delete the duplicate records where all selected columns have same values. You will receive confirmation about the number of records deleted by Excel
Note: If your data has extra spaces at the end of the value (for example ‘Friedrick’ and ‘Friedrick’ then Excel may not be able to detect those duplicates. In this method, we will use VBA code to call the Remove Duplicates function and delete the duplicate records
VBA Code to Remove Duplicate Rows: Syntax
Following is the VBA Syntax and sample VBA macro command to delete duplicate rows from the worksheet using VBA. We are using the Remove Duplicates method of the Cells object of the 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 Code to Remove Duplicate Rows: Examples
Sub sbRemoveDuplicates()
Cells.RemoveDuplicates Columns:=Array(1)
End Sub VBA Code to Remove Duplicate Rows: With Header Example and Syntax
'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
‘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 
