Posted By

a1ias on 11/24/09


Tagged

macro excel duplicates


Versions (?)

Find duplicate cells in excel column


 / Published in: Visual Basic
 

URL: http://support.microsoft.com/kb/213355

  1. Sub FindDups ()
  2. '
  3. ' NOTE: You must select the first cell in the column and
  4. ' make sure that the column is sorted before running this macro
  5. '
  6. ScreenUpdating = False
  7. FirstItem = ActiveCell.Value
  8. SecondItem = ActiveCell.Offset(1, 0).Value
  9. Offsetcount = 1
  10. Do While ActiveCell <> ""
  11. If FirstItem = SecondItem Then
  12. ' Uncomment next line to highlight both repeating lines
  13. ' ActiveCell.Offset(Offsetcount - 1,0).Interior.Color = RGB(255,0,0)
  14. ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
  15. Offsetcount = Offsetcount + 1
  16. SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
  17. Else
  18. ActiveCell.Offset(Offsetcount, 0).Select
  19. FirstItem = ActiveCell.Value
  20. SecondItem = ActiveCell.Offset(1,0).Value
  21. Offsetcount = 1
  22. End If
  23. Loop
  24. ScreenUpdating = True
  25. End Sub

Report this snippet  

You need to login to post a comment.