Solved: Delete rows based on Criteria in two cols

[naruto]

I have data in 2 separate columns in 2 separate sheets..

Sheet 1 Sheet 2
Col A Col A
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10
11
12
13

Result in Sheet 2, Col A
1
2
3
4
5
6
7
8
9

I want to delete all the rows in Sheet 2, Col A that are not present in Sheet 1, Col A using VBA


Please help.


Thanks.

[Simon Lloyd]

Try this:

Sub Delete_Non_Matches()
Dim Rng As Range
Dim LastR, i As Long
With Sheets("Sheet1")
Set Rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
With Sheets("Sheet2")
LastR = .Cells(Rows.Count, 1).End(xlUp).Row
For i = LastR To 1 Step -1
If Application.CountIf(Rng, .Cells(i, 1)) = 0 Then
.Rows(i).Delete
End If
Next i
End With
End Sub
Regards,
SImon

[naruto]

Thank you very much.....Work very well.

[Simon Lloyd]

Naruto glad you're sorted please mark your thread solved!

Regards,
Simon