Find matching data in two worksheets and copy entire row

Closed
Report
-
Posts
1328
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 10, 2022
-
I have one sheet that has about 350k rows and columns A-AW. Column Z contains phone numbers. I have a second sheet that only has phone numbers (about 28k), also in column Z. I want to search the phone numbers in the second sheet and if there is a match on the first one, copy the entire row from the first sheet into a new sheet. Is this possible? Thank you!

1 reply

Posts
1328
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 10, 2022
241
Hello Fairhope221,

Try the following code in a copy of your work book first:-


Sub FindPhoneNos()

    Dim lr As Long
    Dim sValue As Range, c As Range
    
Application.ScreenUpdating = False

Sheet3.UsedRange.Offset(1).ClearContents

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For Each c In Sheet1.Range("Z2:Z" & lr)
    Set sValue = Sheet2.Columns("Z:Z").Find(c.Value)
    If sValue Is Nothing Then GoTo Nextc
    If c.Value = sValue.Value Then
    c.EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
    End If
Nextc:
Next c

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


I'm assuming that your data starts in row2 with headings in row1 of each sheet.

I hope that this helps.

Cheerio,
vcoolio.
0