Mrinmoy Roy
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.
❸ 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.
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.
❸ 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.
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.
❸ 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.
Read More: Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
Similar Readings
- How to Find Matching Values in Two Worksheets in Excel (4 Methods)
- Find Matches or Duplicate Values in Excel (8 Ways)
- Excel Top 10 List with Duplicates (2 Ways)
- Excel Find Similar Text in Two Columns (3 Ways)
- Finding out the number of duplicate rows using COUNTIF formula
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 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.
❸ 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.
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
- How to Compare Two Excel Sheets Duplicates (4 Quick Ways)
- How to Find Duplicates without Deleting in Excel (7 Methods)
- Excel Find Duplicates in Column and Delete Row (4 Quick Ways)
- How to Find Duplicate Rows in Excel (5 Quick Ways)
- Vlookup Duplicate Matches in Excel (5 Easy Ways)
- How to Find Duplicates in Two Different Excel Workbooks (5 Methods)
