Deleting duplicates Macro question

[Bungers]

Hi again,

I have a list of customers with 600 or so names on that
was generated in September. I have another list of
customers who have joined our organisation up until
yesterday. This has about 1000 names on.

I need to filter out the 400 or so customers who have
joined since september.

I've copied both lists onto the same worksheet, and I
have written a macro that removes the duplicate entries.

This works, but it still has all of the
original "September customers" in it.

How do I remove the duplicate entry AND the "Source".

eg.
The list has:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

My duplicate macro will produce a list like this...

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

But I want a macro to remove both "Bens" and both "Daves"
to give a list like this...

(e-mail address removed)
(e-mail address removed)

What would you recommend...??

Thanks... in advance,


Ben

p.s. Here's my Macro... it replaces duplicates with
a "zzz" which I then sort and delete.

Sub deleteduplicates()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1,
ColNum).Value Then
Cells(RowNdx, ColNum).Value = "zzz"
End If
Next RowNdx
End Sub

 

[Peter Atherton]

This seems to work using Excel's Find and Replace function

Sub deleteduplicates2()
Dim rng As Range
Dim c As Variant

Set rng = Selection
For Each c In rng
If Application.WorksheetFunction.CountIf(rng, c) > 1 Then
Selection.Replace What:=c, Replacement:="zzz",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End If
Next c
End Sub


Regards
Peter

 

[Bungers]

Thanks for your help! It comes back with a syntax error,
but I think it's something I'm doing wrong. Will get one
of my VB work people to try to help me!

Cheers!

Ben