Excel VBA to Find Duplicate Values in Range: 7 Examples

Method 1 – Use VBA For Loop to Identify Duplicate Values in a Range

Step 1:

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.

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.

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

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.

Method 2 – VBA Code to Check If Duplicate Values Exist in a Selection

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.

The result window shows 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.

Method 3 – Excel VBA to Find Repeated Values from Selection

Step 1:

Select a range from the dataset.
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

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.

Method 4 – VBA CountIfs Function to Find Matching Values from Selection

Step 1:

Select a range from the dataset.
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.

Get the duplicate data from our selected range.

Method 5 – Find Matching Values from Each Row with Excel VBA

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.

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.

Method 6 – Excel VBA to Find Duplicates from Each Column

Step 1:

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.

Method 7 – Highlight Duplicates within a Selection Using Excel VBA

Step 1:

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. We use the VBA Countif property to check duplicates and view the sum using the MsgBox command.

Step 1:

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