Identify duplicate values in column and highlight them in vba excel

find duplicate vba excel


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


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



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
Selection.Delete Shift:=xlToLeft

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