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

**Table of Contents**hide

## 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 Sub
```

**Code 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 Sub
```

**Code 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 Sub
```

**Code 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)**