ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   compare names and delete duplicates (https://www.excelbanter.com/excel-programming/282502-compare-names-delete-duplicates.html)

Marcia[_2_]

compare names and delete duplicates
 
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





All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com