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”.
How to Use VBA Code to Find Duplicate Rows in Excel: 3 Methods
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: How to Find Duplicate Rows in Excel
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 Repeated Cells in Excel
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 Repeated Numbers in Excel
Practice Section
We’ve provided a practice worksheet in the Excel file.
Download Practice Workbook
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
- How to Filter Duplicates in Excel
- How to Compare Rows for Duplicates in Excel
- Excel Find Duplicate Rows Based on Multiple Columns
- How to Compare Two Excel Sheets for Duplicates
- How to Find Matching Values in Two Worksheets in Excel
- How to Find Duplicates in Excel and Copy to Another Sheet
- Excel VBA to Find Duplicate Values in Range
- How to Find Duplicates in a Column Using Excel VBA
<< Go Back to Find Duplicates in Excel | Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
