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

Excel VBA Course : Beginners to Advanced

We are currently offering our Excel VBA Course at discounted prices. This courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

This course is going to help you to excel your skills in Excel VBA with our real time case studies.

Lets get connected and start learning now. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Similar Posts

Leave a Reply

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