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.

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.

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.

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.

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.