It is very usual in Excel to have duplicate data. We can **find duplicates** in different ways in Excel. In this article, we will discuss how to find duplicate values in range using Excel** VBA**.

**VBA Code to Find Duplicates from Selection (Quick View)**

```
Sub Test_Duplicate_Values_2()
Dim range_1 As Range
Dim array_1 As Variant
Set range_1 = Selection
array_1 = WorksheetFunction.Unique(range_1)
If UBound(array_1) < range_1.Count Then
MsgBox "Duplicate values found"
Else
MsgBox "No duplicate values found"
End If
End Sub
```

**Excel VBA to Find Duplicate Values in Range: 7 Examples**

We will discuss **7** examples of Excel** VBA** to find duplicate values in Excel. We have a dataset of sales of **1st** **7** days of **6** months of **2021 **of a superstore.

**1. Use VBA For Loop to Identify Duplicate Values in a Range**

In this example, we will find the duplicates of a certain column using a **For Loop**. Execute the following steps to do that.

**Step 1:**

- First, go to the
**Sheet Name**section at the bottom border of each cell. Press the right button of the mouse. - Choose the
**View Code**option from the list.

**Step 2:**

**VBA Application**window will open. Go to the**Module**option from the**Insert**tab.

Now, the command window will appear. We will write the **VBA **code here.

**Step 3:**

- Write the following
**VBA**code on the module.

```
Sub Duplicate_with_for_loop()
For n = 5 To 11
If Application.CountIf(Range("C5:C11"), Range("C" & n)) > 1 Then
Range("I" & n).Value = True
Else
Range("I" & n).Value = False
End If
Next n
End Sub
```

**Step 4:**

- Press
**F5**to run the**VBA**code.

Here, we will get **TRUE **and **FALSE **indicating duplicate values in the **Status **column.

**Code Explanation:**

`For n = 5 To 11`

A for loop is applied where the value of **n** ranges from **5** to **11**.

```
If Application.CountIf(Range("C5:C11"), Range("C" & n)) > 1 Then
Range("I" & n).Value = True
Else
Range("I" & n).Value = False
End If
Next n
```

Here, an If condition is applied to count the values of **Column C** of **Range C5:C11. **Print **TRUE **if condition fulfills otherwise** FALSE **at **Column I**.

**Read More: **How to Find Duplicate Rows in Excel

**2. VBA Code to Check If Duplicate Values Exist in a Selection**

We will just check if our desired range contains any duplicates or not in this example. We will use the **Application.InputBox** and **Evaluate** methods together with the **Replace function**.

**Step 1:**

- Press
**Alt+F11**to enter the command module. - Copy the following
**VBA**code on the command module.

```
Sub Test_Duplicate_Values()
Dim range_1 As Range
Set range_1 = Application.InputBox("Please select range:", Type:=8)
If Evaluate(Replace("NOT(AND((COUNTIF(@,@)=1)))", "@", range_1.Address)) = True Then
MsgBox "Duplicate values found"
Else
MsgBox "No duplicate values found"
End If
End Sub
```

**Step 2:**

- Press
**F5**to run the**VBA**code. - A new window will appear to choose a range from the dataset.

**Step 3:**

- Press
**OK**on that window.

Now, the result window is showing whether duplicates are found or not.

**Code Explanation:**

`Dim range_1 As Range`

Declares the variable.

`Set range_1 = Application.InputBox("Please select range:", Type:=8)`

The value of **range_1** variable will be taken from the **InputBox **function.

```
If Evaluate(Replace("NOT(AND((COUNTIF(@,@)=1)))", "@", range_1.Address)) = True Then
MsgBox "Duplicate values found"
Else
MsgBox "No duplicate values found"
End If
```

This If function evaluates duplicates from our selected range. And the **MsgBox **will view the result based on the fulfillment of the condition.

**Read More:** How to Find Repeated Cells in Excel

**3. ****Excel VBA to Find Repeated Values from Selection**

In this example, we will check duplicate values from active cell selection of a range using the **VBA Unique property**.

**Step 1:**

- First, select a range from the dataset.
- Now, hit
**Alt+F11**to enter the**VBA**command module. - Put the following
**VBA**code on that module.

```
Sub Test_Duplicate_Values_2()
Dim range_1 As Range
Dim array_1 As Variant
Set range_1 = Selection
array_1 = WorksheetFunction.Unique(range_1)
If UBound(array_1) < range_1.Count Then
MsgBox "Duplicate values found"
Else
MsgBox "No duplicate values found"
End If
End Sub
```

**Step 2:**

- Press
**F5**to run the**VBA**code.

We get the result after the test.

**Code Explanation:**

```
Dim range_1 As Range
Dim array_1 As Variant
```

These declare the variables.

`Set range_1 = Selection`

This sets the value of the **range_1 **variable from the active sheet selection.

`array_1 = WorksheetFunction.Unique(range_1)`

This performs a worksheet **Unique **function on **range_1 **variable and stores at **array_1**.

```
If UBound(array_1) < range_1.Count Then
MsgBox "Duplicate values found"
Else
MsgBox "No duplicate values found"
End If
```

A condition is set using the If function and **MsgBox **will view the results.

**Read More:** How to Find Repeated Numbers in Excel

**4. VBA CountIfs Function to Find Matching Values from Selection**

We will use the** WorksheetFunction.CountIfs method **and **Unique property** to find duplicate values in this example.

**Step 1:**

- First, select a range from the dataset.
- Then, enter the
**VBA**command module by pressing**Alt+F11**and copy the following**VBA**code on the module.

```
Sub Test_Duplicate_Values_3()
Dim range_1 As Range
Dim array_1 As Variant
Dim n As Integer
Set range_1 = Selection
array_1 = WorksheetFunction.Unique(range_1)
For n = LBound(array_1) To UBound(array_1)
If WorksheetFunction.CountIfs(range_1, array_1(n, 1)) > 1 Then
MsgBox "We can found " & Chr(34) & array_1(1, n) & Chr(34) & _
" more than one time."
Exit Sub
End If
Next n
MsgBox "No Duplicates Found. Continuing on..."
End Sub
```

**Step 2:**

- Press
**F5**to run the**VBA**code.

Here, we get the duplicate data from our selected range.

**Read More: **How to Filter Duplicates in Excel

**5. Find Matching Values from Each Row with Excel VBA**

This **VBA **example will check duplicates row-wise. We will check duplicates from each row and highlight them using the **VBA Countif** and **ColorIndex property**.

**Step 1:**

- Press
**Alt+F11**and enter the**VBA**command module. - Put the following
**VBA**code on that module.

```
Sub Find_Duplicate_From_Row()
Dim cell_1 As Range
Dim row_1 As Integer
Dim range_1 As Range
Dim col_1 As Integer
Dim n As Integer
row_1 = Range(Cells(4, 2), Cells(4, 2).End(xlDown)).Count
col_1 = Range(Cells(4, 2), Cells(4, 2).End(xlToRight)).Count
For n = 5 To row_1 + 3
Set range_1 = Range(Cells(n, 2), Cells(n, col_1 + 1))
For Each cell_1 In range_1
If WorksheetFunction.CountIf(range_1, cell_1.Value) > 1 Then
cell_1.Interior.ColorIndex = 4
End If
Next
Next
End Sub
```

**Step 2:**

- Press
**F5**to run the code.

Here, duplicate cells are highlighted through each row.

**Code Explanation:**

```
Dim cell_1 As Range
Dim row_1 As Integer
Dim range_1 As Range
Dim col_1 As Integer
Dim n As Integer
```

Declare the variables.

`row_1 = Range(Cells(4, 2), Cells(4, 2).End(xlDown)).Count`

Determine the range of **row_1** based on the **VBA **Cells property.

`col_1 = Range(Cells(4, 2), Cells(4, 2).End(xlToRight)).Count`

Determine the range of **col_1 **based on the **VBA Cells property**.

`For n = 5 To row_1 + 3`

Apply a **For** loop where the value of **n** varies from **5** to **row_1+3**

`Set range_1 = Range(Cells(n, 2), Cells(n, col_1 + 1))`

This sets a value for **range_1** variable.

```
For Each cell_1 In range_1
If WorksheetFunction.CountIf(range_1, cell_1.Value) > 1 Then
End If
```

Apply an **If function** within a **For loop** using the **VBA CountIf function**.

`cell_1.Interior.ColorIndex = 4`

Set the color of the cells of the **cell_1** variable.

**Read More:** How to Compare Rows for Duplicates in Excel

**6. Excel VBA to Find Duplicates from Each Column**

This example will search for duplicates of a column. We will check duplicates throughout a column and highlight them. To determine each cell, we used the **VBA Range** and **Cells** properties with the **End property**.

**Step 1:**

- First, hit the
**Alt+F11**buttons and enter the command module. - Write type the
**VBA**code below.

```
Sub Find_Duplicate_From_Column()
Dim cell_1 As Range
Dim row_1 As Integer
Dim range_1 As Range
Dim col_1 As Integer
Dim n As Integer
row_1 = Range(Cells(4, 2), Cells(4, 2).End(xlDown)).Count
col_1 = Range(Cells(4, 2), Cells(4, 2).End(xlToRight)).Count
For n = 1 To row_1
Set range_1 = Range(Cells(5, n), Cells(row_1 + 4, n))
For Each cell_1 In range_1
If WorksheetFunction.CountIf(range_1, cell_1.Value) > 1 Then
cell_1.Interior.ColorIndex = 8
End If
Next
Next
End Sub
```

**Step 2:**

- Hit the
**F5**button to run the code.

**Read More:** Excel Find Duplicate Rows Based on Multiple Columns

**7. Highlight Duplicates within a Selection Using Excel VBA**

We highlight the duplicate cells from the selection using Excel** VBA**. First, we identify the duplicate cells using the **VBA Countif **property. Then, highlight them by the **ColorIndex** property.

**Step 1:**

- First, select cells from the dataset using the cursor.
- Enter the command module by pressing the
**Alt+F11**keys. - Copy the
**VBA**code and paste that.

```
Sub Find_Duplicate_From_Selection()
Dim range_1 As Range
Dim cell_1 As Range
Set range_1 = Selection
For Each cell_1 In range_1
If WorksheetFunction.CountIf(range_1, cell_1.Value) > 1 Then
cell_1.Interior.ColorIndex = 10
End If
Next
End Sub
```

**Step 2:**

- Press the
**F5**button and highlight the duplicate cells from our selection.

**Read More:** How to Compare Two Excel Sheets for Duplicates

**How to Count Duplicate Values in Range Using VBA Codes**

In the previous VBA examples, we search for duplicates using Excel **VBA**. But, we want to count the number of duplicate values this time. We use the **VBA Countif** property to check duplicates and then view the sum using the **MsgBox **command.

**Step 1:**

- Now, go to the command module by pressing
**Alt+F11**. - Type the
**VBA**code on that module.

```
Sub Count_Duplicate_values()
Dim m As Integer
Dim cell_1 As Range
Dim range_1 As Integer
range_1 = Range("C5:H11").Count
m = 0
For Each cell_1 In Range("C5:H11")
If WorksheetFunction.CountIf(Range("C5:H11"), cell_1.Value) > 1 Then
m = m + 1
End If
Next
MsgBox m
End Sub
```

**Step 2:**

- Then run the code by pressing
**F5**.

There are multiple duplicate values and we count all the duplicates here.

**Read More: **How to Find Matching Values in Two Worksheets in Excel

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we have shown how to search the duplicate values in a range with Excel** VBA**. We also count the number of duplicates. I hope this will satisfy your needs. Please give your suggestions in the comment box.

**Related Articles**

- How to Find Duplicates in Excel and Copy to Another Sheet
- How to Find Duplicates in a Column Using Excel VBA
- How to Use VBA Code to Find Duplicate Rows in Excel

**<< Go Back to Find Duplicates in Excel**** | Duplicates in Excel | Learn Excel**