Sunday, February 7, 2010

How do I highlight duplicate rows in Excel without deleting them?

There is a Delete Duplicates function in Excel that makes it very easy to delete any duplicate data in your spreadsheet. However, sometimes you may not want to delete that data, you just want to know that there are duplicates. One way of doing this is with a simple macro. To highlight and draw attention to duplicates in a Microsoft Excel spreadsheet with a bold red color create this macro (alt+F11 opens the macro editor):

Sub DupsinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".