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 SubExcel 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 11A 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 nHere, 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 RangeDeclares 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 IfThis 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 VariantThese declare the variables.
Set range_1 = SelectionThis 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 IfA 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 IntegerDeclare the variables.
row_1 = Range(Cells(4, 2), Cells(4, 2).End(xlDown)).CountDetermine the range of row_1 based on the VBA Cells property.
col_1 = Range(Cells(4, 2), Cells(4, 2).End(xlToRight)).CountDetermine the range of col_1 based on the VBA Cells property.
For n = 5 To row_1 + 3Apply 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 IfApply an If function within a For loop using the VBA CountIf function.
cell_1.Interior.ColorIndex = 4Set 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.
