Advanced Search

Find duplicate cells in excel column

/ Published in: Visual Basic
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.