compare names and delete duplicates

M

Marcia

I am trying to change a macro. Originally I had a worksheet (DL info) with
Lastname, Firstname format in column A and the macro below worked to compare
to worksheet (Krofta) and delete duplicates. The worksheet Kroft had
LastName in column A and FirstName in column B.

Since then I've grown and am now using a macro to split the names on Kroft
worksheet into Lastname in column A and Firstname in column B - just like my
DL info worksheet. I'm not getting the macro changed properly so it will
now compare LastName to LastName and FirstName to FirstName. My goal is to
compare the 2 worksheets and delete any duplicates from the Krofta
worksheet.

Macro will follow. Any help is much appreciated. Thanks!

Sub DeleteMatch()
'This compares LastName and FirstName on DL info to Username on Krofta
'testing to try to get to compare LastName to LastName and FirstName to
Firstname
Dim x, Rng1 As Range, LstRow As Integer
Dim LastName, FirstName, FirstAdd

With Worksheets("Krofta")
Set Rng1 = .Range(.Cells(1, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With

With Worksheets("DL info")
LstRow = Cells(Rows.Count, "A").End(xlUp).Row
End With

For I = LstRow To 1 Step -1
LastName = Worksheets("DL info").Cells(I, "D")
FirstName = Worksheets("DL info").Cells(I, "B")
With Rng1
Set x = .Find(LastName, , xlValues)
If Not x Is Nothing Then 'Match found
FirstAdd = x.Address
Do
If Right(x, Len(x) - InStr(x, ",") - 1) = FirstName Then
'Both First and Last Names Match
Worksheets("DL").Rows(I).Delete Shift:=xlUp
Exit Do
Else
Set x = .FindNext(after:=Cells(x.Row, 1))
End If
Loop Until x Is Nothing Or x.Address = FirstAdd
End If
End With
Next

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top