Macro-pawan

[Guest]

I have 2 sheets in same .xls file. In second sheet-column A, I have list of
some values, say 100 values. In sheet 1-column B, I have list of some 2000
values. Values of sheet 2-columnA are present in sheet1-columnB. Multiple
instances are also there. I want to find all these values in sheet1-columnB
AND OTHER ROWS OF SHEET1 (which doesn't contain values from sheet2) ARE TO BE
DELETED. How can we write macro for this?
Thank you in advance.
-Pawan

 

[Nigel]

One method follows, it determines length of each data set and reads sheet 1
col B from last to first row and deletes the row if it does not find the
value on sheet 2 column 1.

Sub dedupe()
Application.ScreenUpdating = False
Dim sh1 As Worksheet, sh2 As Worksheet
Dim xlr1 As Long, xlr2 As Long, xr1 As Long, xr2 As Long, xNF As Boolean
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

xlr1 = sh1.Cells(Rows.Count, 2).End(xlUp).Row
xlr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row

For xr1 = xlr1 To 1 Step -1
xNF = True
For xr2 = 1 To xlr2
If sh1.Cells(xr1, 2) = sh2.Cells(xr2, 1) Then
xNF = False
Exit For
End If
Next
If xNF Then Rows(xr1).Delete Shift:=xlUp

Next
Application.ScreenUpdating = True
End Sub