How to Use VBA Code to Find Duplicate Rows in Excel: 3 Methods

Method 1 – Utilizing Range.Offset Property in VBA Code to Find Duplicate Rows in Excel

Steps:

From the Developer tab >>> select Visual Basic.

Press ALT + F11 to do this.

vba code to find duplicate rows in excel

The Visual Basic window will appear.

From Insert >>> click Module.

Copy and paste or type the following code.

Sub FindDuplicateRows()
    Dim cRange As Range
    Dim cSearch As Range
    Dim acSearch As String
    Dim cDuplicate As String
    Dim x As Integer
    Dim name As String
    Range("A5:D10").Interior.Pattern = xlNone
    cColor = 6
    Set cRange = Range("A5:D10")
    For Each i In cRange
        x = WorksheetFunction.CountIf(Columns(2), i) - 1
        If x > 0 Then
            cDuplicate = i & i.Offset(0, 1).Value & i.Offset(0, -1).Value
            Set cSearch = i
            For x = 1 To x
                Set cSearch = cRange.Find(What:=i, After:=cSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                acSearch = cSearch.Address
                If cDuplicate = Range(acSearch).Value & Range(acSearch).Offset(0, 1).Value & Range(acSearch).Offset(0, -1).Value Then
                    With Union(Range(acSearch), Range(acSearch).Offset(0, 1), Range(acSearch).Offset(0, -1), i, i.Offset(0, 1), i.Offset(0, -1)).Interior
                        .ColorIndex = cColor
                    End With
                End If
            Next x
        End If
        cColor = cColor + 1
    Next i
End Sub

Code Breakdown

We’re calling the Sub Procedure “FindDuplicateRows” and assigning our variables.
Remove any color from the last run of our code with the Range.Interior.Pattern = xlNone statement. Use the Set statement to define our range.
The For Next loop comes into the action.
x = WorksheetFunction.CountIf(Columns(2), i) – 1
- This counts the number of times the value occurs. 1 is subtracted to search two times.
cDuplicate = i & i.Offset(0, 1).Value & i.Offset(0, -1).Value
- This is how we define our duplicates.
For x = 1 To x
- Here, we’re searching for more than one occurrence of a value.
Check and match all the rows. If there is a match, the two rows will be highlighted. Use the ColorIndex property for that. For more than one match the color will be changed to “cColor”.

Save and close the Visual Basic window to go back to our sheet.
From the Developer tab >>> select Macros.

The Macro dialog box will appear.

Select “FindDuplicateRows” from the list and click Run.

Find duplicate rows in Excel using a VBA code.

Method 2 – VBA Code to Find Duplicate Rows in Excel for Unique Cell Values Only

Steps:

Bring up the Module window as shown in method 1.
Type the following code.

Sub DuplicateRows2()
Dim cRange As Range
Dim cCell As Range
Set cRange = Range("B5:D10")
For Each cCell In cRange
    If WorksheetFunction.CountIf(cRange, cCell.Value) > 1 Then
    cCell.Interior.ColorIndex = 34
    End If
Next
End Sub

Code Breakdown

We called the Sub Procedure “DuplicateRows2”.
Assign all the variables using the Range property.
Use the Set statement to define our range.
Use the WorksheetFunction.Countif to set the rows to highlight duplicates inside a For Next loop.

Save the code and close it to go back to our sheet.
Bring up the Macro dialog box as shown in method 1.
Select “DuplicateRows2” and click Run.

See the duplicate rows are highlighted.

Note: This code works on a cell-by-cell basis. If there is a duplicate value in a cell. This code will highlight that, as we can see in the picture below. If your dataset doesn’t have unique values, you should use either method 1 or 3.

Method 3 – Combining Nested If and For Next Loop to Formulate a VBA Code to Find Duplicate Rows in Excel

Use VBA Nested If and For Next Loop to formulate a code to find duplicate rows.

Steps:

Bring up the Module window as shown in method 1.
Type the following code.

Sub DuplicateRows3()
    Dim xRow As Integer, cRow As Integer, i As Integer
    xRow = Cells(Rows.Count, 1).End(xlUp).row
    For i = 2 To xRow
        For cRow = i + 1 To xRow
            If Range("A" & cRow) = Range("A" & i) Then
                If Range("B" & cRow) = Range("B" & i) Then
                    If Range("C" & cRow) = Range("C" & i) Then
                        Range("A" & cRow & ":C" & cRow).Interior.Color = vbGreen
                        Range("A" & i & ":C" & i).Interior.Color = vbGreen
                    End If
                End If
            End If
        Next cRow
    Next i
End Sub

Code Breakdown

We’re calling the Sub Procedure “DuplicateRows3”. Assign the variable types.
Count the last used row with the End(xlUp) property and keep the value in the “xRow” variable.
Use 2 For Loops. Use the Nested If statement to check if any rows match the others.
Highlight it with “vbGreen” from the Interior.Color property.