How to Find Duplicates in a Column Using Excel VBA (5 Ways)

Microsoft Excel offers multiple ways to find and remove duplicate values in a column in a worksheet. But in this article, you will learn 5 ways to find duplicates in a column in Excel using VBA codes only.

How to Find Duplicates in a Column Using Excel VBA: 5 Ways

1. Find Duplicates for Range of Cells in a Column

You can use this method to return TRUE if a value is duplicated or FALSE if a value is unique.

For that,

❶ Press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

❸ Copy the following VBA code.

Sub FindDuplicateValues()

Dim xWs As Worksheet

Set xWs = Worksheets("VBA1")

For m = 5 To 12
If Application.WorksheetFunction.CountIf(xWs.Range("B5:B12"), xWs.Range("B" & m)) > 1 Then
xWs.Range("C" & m).Value = True
Else
xWs.Range("C" & m).Value = False
End If
Next m

End Sub

❹ Paste and Save the code in the VBA editor.

Breakdown of the Code

First of all, I’ve declared a variable xWs as
Then I assigned a worksheet named “VBA1” to the variable.
After that, I used the..Else statement to return True for the duplicate values and Flase for the unique values with the help of the VBA Countif function.

❺ Come back to your Excel worksheet and press ALT + F8 to open the Macro dialog box.

❻ Select the macro FindDuplicateValues and hit the Run button.

Now you will get TRUE for the duplicate values and FALSE for the unique values in their adjacent cells.

2. Select a Range and Highlight Duplicates in a Column Using Excel VBA

This VBA code will allow you to manually select a range and highlight any duplicate values if there exist any.

Now follow the steps below to get the usage steps.

❶ First of all, press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

❸ Copy the following VBA code.

Sub SelectAndDetectDups()

Dim xRng1 As Range
Dim xCell1 As Range

Set xRng1 = Selection

For Each xCell1 In xRng1
If WorksheetFunction.CountIf(xRng1, xCell1.Value) > 1 Then
xCell1.Interior.ColorIndex = 3
End If
Next

End Sub

❹ Paste and Save the code in the VBA editor.

Breakdown of the Code

At first, I declared two variables these are XRng1 and xCell as Range.
Then I wrote a Set statement to assign the Selection area to the first variable.
After that, I used a For loop to go through the Selection then used the IF statement and the Countif function to highlight the duplicate values.

❺ Come back to your Excel worksheet and select a range.

❻ Press ALT + F8 to open the Macro dialog box.

❼ Select the macro SelectAndDetectDups and hit the Run button.

Now you will see all the duplicate values are highlighted in your selected area.

3. Detect and Highlight New Entry Duplicates in a Column

The following VBA code can automatically highlight all the duplicate values inside a worksheet.

It also can highlight a value just after you’ve inserted it if the value matches with any of the existing entities.

To use the code,

❶ First of all, press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

❸ Then double-click on a worksheet to insert the VBA code from the Microsoft Excel Objects list.

A Sheet editor will appear.

Now, select Worksheet from the General.

Next, expand the Declarations drop-down to select Change.

❹ After that, Copy the following VBA code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 2 Then Exit Sub

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Dim xRng1 As Range
Dim xRng2 As Range

Set xRng1 = Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)

For Each xRng2 In xRng1
xRng2.Offset(0, 0).Font.Color = vbBlack

If Application.Evaluate("COUNTIF(" & xRng1.Address & "," & xRng2.Address & ")") > 1 Then
xRng2.Offset(0, 0).Font.Color = vbRed
End If
Next xRng2

Set xRng1 = Nothing

ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

❺ Paste and Save the code in the VBA editor.

Breakdown of the Code

Here, I used a Private Sub to apply the code within the worksheet where the type is Change so that every time I made any changes or insert any new value it will check whether the value is Duplicate or Unique.

I used an If statement to ignore the 2nd row if it contains any header.
Then I assigned False to the ScreenUpdating function.
After that, I declared two variables.
I used a For loop and If statement to set font color black for the unique values and red for the duplicate values.
Finally, I updated all the error handler functions as True.

❻ Now go back to your worksheet and insert a new value.

❼ Hit the ENTER button.

Now you will your latest entity is highlighted if it is a duplicate value.

4. Find and Highlight Duplicates in a Column Using Excel VBA

You can use this method to find and highlight the duplicate values in a range in your Excel worksheet.

For that,

❶ First of all, press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

❸ Copy the following VBA code.

Option Explicit

Sub HighlightDupsInARange()

Application.ScreenUpdating = False
Range("F5:F" & Cells(Rows.Count, 5).End(xlUp).Row) = "=COUNTIF($C$5:$C5,C5)>1"
Range("F:F").AutoFilter 1, "True"
Range("C4:C" & Cells(Rows.Count, 6).End(xlUp).Row).Interior.Color = vbCyan
Range("F:F").AutoFilter
Range("F:F").ClearContents
Application.ScreenUpdating = True

End Sub

❹ Paste and Save the code in the VBA editor.

Breakdown of the Code

I used the COUNTIF function to count each of the entity’s number of appearances.
Then I used the AutoFilter to filter out all the duplicate values and mark them as True in column F.
After that, I used the Color property to highlight all the duplicate values using the Cyan color.
Finally, I used the AutoFilter again to Filter out all the Trues from the F column and deleted them using the ClearContents.

❺ Come back to your Excel worksheet and press ALT + F8 to open the Macro dialog box.

❻ Select the macro HighlightDupsInARange and hit the Run button.

After that, you will see all the duplicate values highlighted in your insertion range as in the picture below:

Read More: How to Filter Duplicates in Excel

5. Use VBA to Find and Delete Duplicates in a Column

You can use this method to find the duplicates in a column and then delete them all.

Now follow the steps below to get the usage steps.

❶ First of all, press ALT + F11 to open the VBA editor.

❷ Then go to Insert >> Module.

❸ Copy the following VBA code.

Sub DeleteDupsInARange()

Application.ScreenUpdating = False
Range("F5:F" & Cells(Rows.Count, 5).End(xlUp).Row) = "=COUNTIF($E$5:$E5,E5)>1"
Range("F:F").AutoFilter 1, "True"
Range("E5:E" & Cells(Rows.Count, 6).End(xlUp).Row).EntireRow.Delete
Range("F:F").AutoFilter
Range("F:F").ClearContents
Application.ScreenUpdating = True

End Sub

❹ Paste and Save the code in the VBA editor.

Breakdown of the Code

I used the COUNTIF function to count each of the entity’s number of appearances.
Then I used the AutoFilter to filter out all the duplicate values and mark them as True in column F.
After that, I used the Delete property to delete all the duplicate values.
Finally, I used the AutoFilter again to Filter out all the Trues from the F column and deleted them using the ClearContents.

Now come back to your Excel worksheet.

I will use the above code to delete all the duplicates from the dataset of the following screenshot.

❺ Now press ALT + F8 to open the Macro dialog box.

❻ Select the macro DeleteDupsInARange and hit the Run button.

Finally, you will see all the duplicate values have been deleted leaving the unique values only.

Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Find Duplicates in a Column.xlsm

Conclusion

To sum up, we have discussed 5 methods to find duplicates in a column Using Excel VBA. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.

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

Get FREE Advanced Excel Exercises with Solutions!

How to Find Duplicates in a Column Using Excel VBA (5 Ways)

How to Find Duplicates in a Column Using Excel VBA: 5 Ways

1. Find Duplicates for Range of Cells in a Column

2. Select a Range and Highlight Duplicates in a Column Using Excel VBA

3. Detect and Highlight New Entry Duplicates in a Column

4. Find and Highlight Duplicates in a Column Using Excel VBA

5. Use VBA to Find and Delete Duplicates in a Column

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply