Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

We’re going to show you 3 methods of how to use VBA code to find duplicate rows in Excel. To demonstrate our methods, we’ve taken a dataset with 3 columns: “Name”, “Car Model”, and “Car Maker”.

vba code to find duplicate rows in excel


Download Practice Workbook


3 Ways to Use VBA Code to Find Duplicate Rows in Excel

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

For the first method, we’re going to use the VBA Range.Offset property and ColorIndex property with VBA If statement and For Next Loop to find duplicate rows in Excel. Moreover, notice there are 2 sets of duplicates in our dataset.

Steps:

  • Firstly, from the Developer tab >>> select Visual Basic.

Alternatively, you can press ALT + F11 to do this.

vba code to find duplicate rows in excel

The Visual Basic window will appear.

  • Secondly, from Insert >>> click on Module.

  • Thirdly, 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

  • Firstly, we’re calling our Sub ProcedureFindDuplicateRows”. After that, we’re assigning our variables.
  • Then, we’re removing any color from the last run of our code with the Range.Interior.Pattern = xlNone statement. After that, we’re using the Set statement to define our range.
  • After that our For Next loop comes into the action.
  • x = WorksheetFunction.CountIf(Columns(2), i) – 1
    • This counts the number of times the value occurs. Moreover, 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.
  • Then, we’re checking and matching all the rows. If there is a  match, then the two rows will be highlighted. We’re using the ColorIndex property here for that. For more than one match the color will be changed for our “cColor” variable.

vba code to find duplicate rows in excel

  • Then Save and close the Visual Basic window to go back to our sheet.
  • After that, from the Developer tab >>> select Macros.

The Macro dialog box will appear.

  • Finally, select “FindDuplicateRows” from the list and click on Run.

vba code to find duplicate rows in excel

Thus, we’ll find duplicate rows in Excel using a VBA code.

Read More: Excel VBA to Find Duplicate Values in Range (7 Examples)


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

We’re going to use VBA WorksheetFunction.Countif method along with the VBA If statement to find duplicate rows.

vba code to find duplicate rows in excel

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’re calling our Sub ProcedureDuplicateRows2”.
  • After that, we’re assigning all the variables using the Range property.
  • Then, we’re using the Set statement to define our range.
  • Finally, we’re using the WorksheetFunction.Countif to set the rows to highlight duplicates inside a For Next loop.

  • Thirdly, Save the code, and close it to go back to our sheet.
  • Then, bring up the Macro dialog box as shown in method 1.
  • After that, select “DuplicateRows2” and click on Run.

vba code to find duplicate rows in excel

Consequently, we’ll see the duplicate rows are highlighted.

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

vba code to find duplicate rows in excel

Read More: How to Find Duplicates in a Column Using Excel VBA (5 Ways)


Similar Readings


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

For the last method, we’re going to 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 our Sub ProcedureDuplicateRows3”. After that, we’ve assigned our variable types.
  • Then, we’re counting the last used row with the End(xlUp) property and then kept the value in the “xRow” variable.
  • After that, we’re using 2 For Loops. Moreover, inside that loop, we’re using the Nested If statement to check if any of the rows match with the others.
  • If a match is found, then we’re highlighting it with “vbGreen” from the Interior.Color property.

vba code to find duplicate rows in excel

  • Thirdly, Save the code, and close it to go back to our sheet.
  • Then, bring up the Macro dialog box as shown in method 1.
  • After that, select “DuplicateRows3” and click on Run.

In conclusion, we have achieved our goal of finding duplicate rows in Excel.

vba code to find duplicate rows in excel

Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)


Practice Section

We’ve provided a practice worksheet in the Excel file.


Conclusion

We’ve shown you 3 methods of how to use VBA code to find duplicate rows in Excel. If you face any problems, you may comment below for assistance. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo