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

vba code to find duplicate rows in excel

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

vba code to find duplicate rows in excel

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:

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 ProcedureDuplicateRows2”.
  • 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.

vba code to find duplicate rows in excel

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.

vba code to find duplicate rows in excel


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:

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

vba code to find duplicate rows in excel

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

You achieved the goal of finding duplicate rows in Excel.

vba code to find duplicate rows in excel


Download Practice Workbook


Related Articles


<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo