Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare columns for duplicates | New Users to Excel | |||
compare and merge duplicates | New Users to Excel | |||
Compare Duplicates | Excel Discussion (Misc queries) | |||
How to compare two columns and remove duplicates? | Excel Discussion (Misc queries) | |||
how do i compare two columns and remove duplicates? | New Users to Excel |