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.
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.
- 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.
Download Practice Workbook
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 Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!