DELETING ROWS IN SPREADSHEET BASED ON CRITERIA IN ANOTHER SHEET

[cali-novice]

I have a spreadsheet which has a single column with a few rows of names. I have a second spreadsheet which has 4 columns. The first column contains names. There may be multiple rows with the same name in the first column. I need to keep only those rows in the second spreadsheet where the names in first column match the names in the first spreadsheet. The other rows must be deleted.

Thanks for your help.

[MARK858]

Try the code below, change the sheetnames to suit.


Sub CCCCB()
Dim c As Range, DeleteRange As Range
For Each c In Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
If WorksheetFunction.CountIf(Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row), c) = 0 Then
If DeleteRange Is Nothing Then
Set DeleteRange = Rows(c.Row)
Else
Set DeleteRange = Union(DeleteRange, Rows(c.Row))
End If
End If
Next
DeleteRange.Delete
End Sub