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.


Download Practice Workbook

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


5 Ways to Find Duplicates in a Column Using Excel VBA

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: Excel VBA to Find Duplicate Values in Range (7 Examples)


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 Highlight Duplicate Rows in Excel (3 Ways)


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: Find Duplicates in Two Columns in Excel (6 Suitable Approaches)


Similar Readings


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 Find, Highlight & Remove 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: Excel Find Duplicate Rows Based on Multiple Columns


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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo