Rafiul Haq
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”.
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.
- Firstly, from the Developer tab >>> select Visual Basic.
Alternatively, you can press ALT + F11 to do this.
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 SubCode Breakdown
- Firstly, we’re calling our Sub Procedure “FindDuplicateRows”. 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.
- 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.
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.
Steps:
- Firstly, bring up the Module window as shown in method 1.
- Secondly, 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 SubCode Breakdown
- We’re calling our Sub Procedure “DuplicateRows2”.
- 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.
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.
Read More: How to Find Duplicates in a Column Using Excel VBA (5 Ways)
Similar Readings
- How to Highlight Duplicate Rows in Excel (3 Ways)
- How to Find Matching Values in Two Worksheets in Excel (4 Methods)
- Find Matches or Duplicate Values in Excel (8 Ways)
- Excel Top 10 List with Duplicates (2 Ways)
- Excel Find Similar Text in Two Columns (3 Ways)
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:
- Firstly, bring up the Module window as shown in method 1.
- Secondly, 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 SubCode Breakdown
- We’re calling our Sub Procedure “DuplicateRows3”. 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.
- 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.
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
- Excel Formula to Find Duplicates in One Column
- How to Find Duplicates in Two Different Excel Workbooks (5 Methods)
- How to Compare Two Excel Sheets Duplicates (4 Quick Ways)
- Excel Find Duplicate Rows Based on Multiple Columns
- How to Vlookup Duplicate Matches in Excel (5 Easy Ways)
- How to Find Duplicates in Excel Workbook (4 Methods)
