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.
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
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.