# Excel VBA to Find Duplicate Values in Range (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

### 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. ### 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. ## 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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  