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.
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.
Post you may like