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

Get FREE Advanced Excel Exercises with Solutions!

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.

Insert a new module to Find Duplicates in a Column Using Excel VBA

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.

VBA code to Find Duplicates for Range of Cells in a Column

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.

Read More: How to Find Duplicate Rows in Excel


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.

Insert a new module to Find Duplicates in a Column Using Excel VBA

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.

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

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.

Result: Select a Range and Highlight Duplicates in a Column Using Excel VBA

Read More: How to Find Repeated Cells in Excel


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.

Insert a new module to Find Duplicates in a Column Using Excel VBA

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

VBA Code: Detect and Highlight New Entry Duplicates in a Column

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.

Read More: How to Find Repeated Numbers in Excel


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.

Insert a new module to Find Duplicates in a Column Using Excel VBA

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.

VBA Code: Find and Highlight Duplicates in a Column Using Excel VBA

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.

Insert a new module to Find Duplicates in a Column Using Excel VBA

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.

Using VBA to Find and Delete Duplicates in a Column

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.

Read More: How to Compare Rows for Duplicates in Excel


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo