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

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.

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

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.

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

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.

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

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.

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
End If

cell_1.Interior.ColorIndex = 4

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