Identify duplicate values in column and highlight them in vba excel

find duplicate vba excel

Objective

To highlight and identify duplicate values in a column using VBA Excel.

Approach

Here we had the names of multiple cities in a column A, out of these city names some of them are duplicate values, which are repeating themselves.

Here first we have used the “countif” function in cell B2 to find how many times value in cell A2 is repeating itself in column A. Then we dragged the formula in cell B2 to down to corresponding last populated row in column A.

Then we checked which cells in column B had value 2 or greater, if value is greater than or equal to 2, then we have changed the colour of corresponding cell in column A as yellow and stored the text “Duplicate” in corresponding cell in column C. Once we loop through each cell in column A, we delete the column B, which had temporary calculations.

 

find duplicate vba excel

 

Code

Sub HighlightDuplicateValues()

Dim ValueToBeChecked As String
Dim ColumnRange As String


'Find last populated row
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row


ColumnRange = "A$2:A$" & LastPopulatedRow
ValueToBeChecked = "A2"


'Apply the countif formula in 2nd row
Range("B2").Formula = "=countif(" & ColumnRange & "," & ValueToBeChecked & " )"
 
'Drag the formula down to last populated row
Range("B2: " & "B" & LastPopulatedRow).FillDown

For i = 2 To LastPopulatedRow
If Range("B" & i).Value >= 2 Then
Range("A" & i).Interior.Color = vbYellow
Range("C" & i).Value = "Duplicate"

End If
Next i

'Delete temporary calculation
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

End Sub

 

Once we execute the VBA code, we can see that city names which were repeating were highlighted.

 

find duplicate vba excel

 

Post you may like

Filter data using auto filter – VBA Excel