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.

 

VBA Code to remove duplicate rows

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

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *