Macro to lookup duplicates

C

Constantly Amazed

Hi

Using Excel 2000 I would like to incorporate a macro which allowed the user
to select the first cell in any column of sorted data and then compare each
entry and remove any duplicate lines. It would continue to do this until the
next cell in the chosen column did not contain an entry.
ie The list
A
B
B
C
D
E
E
E

Should produce
A
B
C
D
E

I would be very grateful for assistance on this.

Regards
 
A

Ardus Petus

Turn on macro recorder and use Data>Filter>Advanced Filter with no
Duplicates.

HTH
 
C

Constantly Amazed

Thanks for the suggestion.

However, I would like a 'clean' list not just a filtered list so I would
prefer the lines containing duplicates to be deleted.
 
R

Richard Buttrey

One way.

Select the first cell in the range and then run the macro below


Sub DeleteDuplicate()
Dim x As Integer
Do While ActiveCell.Offset(x + 1, 0) <> ""
If ActiveCell.Offset(x, 0) = ActiveCell.Offset(x + 1, 0) Then
ActiveCell.Offset(x + 1, 0).Delete Shift:=xlUp
Else
x = x + 1
End If
Loop
End Sub

HTH


Thanks for the suggestion.

However, I would like a 'clean' list not just a filtered list so I would
prefer the lines containing duplicates to be deleted.
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
C

Constantly Amazed

Hi Richard

Thanks that does the trick although I would like the macro to delete the
whole row in which the duplicate occurs ratehr than just the cell. If you
can advise how the code needs to be changed toi do this I will be very
greatful. but otherwise I'll have a play.

Thanks
 
R

Richard Buttrey

Hi,

Just change the code with the .delete instruction. i.e.

Sub DeleteDuplicate()
Dim x As Integer
Do While ActiveCell.Offset(x + 1, 0) <> ""
If ActiveCell.Offset(x, 0) = ActiveCell.Offset(x + 1, 0) Then
ActiveCell.Offset(x + 1, 0).EntireRow.Delete
Else
x = x + 1
End If
Loop
End Sub

HTH




Hi Richard

Thanks that does the trick although I would like the macro to delete the
whole row in which the duplicate occurs ratehr than just the cell. If you
can advise how the code needs to be changed toi do this I will be very
greatful. but otherwise I'll have a play.

Thanks
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top