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

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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

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.

"Ardus Petus" wrote:

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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

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


On Fri, 14 Jul 2006 04:21:02 -0700, Constantly Amazed
wrote:

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.

"Ardus Petus" wrote:

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

"Richard Buttrey" wrote:

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


On Fri, 14 Jul 2006 04:21:02 -0700, Constantly Amazed
wrote:

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.

"Ardus Petus" wrote:

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

"Constantly Amazed" wrote:

Hi Richard

Thanks. That has worked fine and I made a modification because I wanted the whole row deleted.


"Richard Buttrey" wrote:

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


On Fri, 14 Jul 2006 04:21:02 -0700, Constantly Amazed
wrote:

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.

"Ardus Petus" wrote:

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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




On Mon, 17 Jul 2006 04:57:01 -0700, Constantly Amazed
wrote:

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

"Richard Buttrey" wrote:

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


On Fri, 14 Jul 2006 04:21:02 -0700, Constantly Amazed
wrote:

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.

"Ardus Petus" wrote:

Turn on macro recorder and use DataFilterAdvanced Filter with no
Duplicates.

HTH
--
AP

"Constantly Amazed" a écrit
dans le message de news:
...
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




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________