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
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