VBA Code to Remove Duplicate Records
Working with huge data is always exciting and challenging. From 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.
Method 1
In the first method, let’s make use of Excel 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 Data ribbon and click on Remove Duplicates button

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

Note: If your data has extra spaces at the end of the value (example ‘Friedrick’ and ‘Friedrick ’) then Excel may not be able to detect those duplicates.
Method 2
In this method, we will use VBA code to call RemoveDuplicates function and delete the duplicate records
Code:
Sheet1.Range("A1:D" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Array(1, 2), xlYes
Explanation:
In the code, we have used dynamic range selection where last row is identified using Ctrl + Up Key on first column of the sheet. The array parameter contains the column positions in the selected range on basis of which duplicates needs to be identified and removed
Note: If your data has extra spaces at the end of the value (example ‘Friedrick’ and ‘Friedrick ’) then Excel may not be able to detect those duplicates.
Method 3
Few VBA developers likes to write their own codes to identify the duplicate records. One of reason is to avoid extra space issue mentioned in Method 1 and Method 2. Also, sometimes the logic of duplicate identification needs to be customized. Let’s have a look at the code.
Code:
Sub RemoveDuplicateRecords()
Dim lOuterCounter As Long
Dim lInnerCounter As Long
Dim lLastRow As Long
'Find last row of the sheet
lLastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
'Run the outer counter loop from 2 to last row
For lOuterCounter = 2 To lLastRow
'Run the inner counter loop from Outer counter + 1 to last row
For lInnerCounter = lOuterCounter + 1 To lLastRow
'Check if column A and B are matching in Inner and Outer loops
If Trim(Sheet1.Range("A" & lCounter).Value) = Trim(Sheet1.Range("A" & lInnerCounter).Value) And _
Trim(Sheet1.Range("B" & lCounter).Value) = Trim(Sheet1.Range("B" & lInnerCounter).Value) Then
'Delete inner counter record
Sheet1.Rows(lInnerCounter).Delete
'Reduce inner counter by 1
lInnerCounter = lInnerCounter - 1
'Reduce last row by 1
lLastRow = lLastRow - 1
Exit For
End If
Next lInnerCounter
Next lCounter
MsgBox "Done"
End Sub
Explanation:
In this code, we are using two loops. The first outer loop is going through each record in the sheet. The second inner loop is going through rest records of the sheet and checking if any record is matching with outer loop record. If any record is identified as duplicate, then code will delete the inner loop record and reduce the loop counter and last row by one.
Method 4
Here we have written a VBA function that can be used as an Excel formula. The function returns True if the record is found as duplicate.

Code:
Public Function IsDuplicate(DataRange As range, CriteriaRange As range) As Boolean
Dim lRow As Long
Dim lColumn As Long
For lRow = 1 To DataRange.Rows.Count - 1
IsDuplicate = True
For lColumn = 1 To CriteriaRange.Columns.Count - 1
If Trim(DataRange(lRow, lColumn).Value) <> Trim(CriteriaRange(1, lColumn).Value) Then
IsDuplicate = False
Exit For
End If
Next
If IsDuplicate = True Then Exit Function
Next
End Function
How to use this function:
Write this function in your Excel worksheet where you want to find duplicate records.

In the first parameter of the function, select the range which needs to be used as data range. In the second parameter, select the range which needs to be used as criteria range.

Note: Do not select same row in both parameters else it will consider the record as duplicate.