Thanks Nigel, I have several 'lists' in Sheet1 Column A that do not relate
to the search I am doing. In saying that I cannot specify a set range as my
'source' range can change. Would I be better specifying a Range name for my
'source' data


"Nigel" wrote in message
...
Hi John
This code will scan your entire list in column A on sheet1 and check if
the
value found is in the list in column A on sheet2
If the value on sheet1 is not found on sheet2 then the entire row on
sheet1
is deleted and all rows shifted up.
It adjusts for varying lengths of data in both sheet1 and sheet2.

Public Sub SelectiveDelete()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

Cheers
Nigel

"John" wrote in message
...
What code should I use if I want to delete entire Rows (and then move
all
other rows below up) if the value in say Sheet1 A1:A.... is not within a
named range, say 'Product Numbers'.

I may have to set-up a Dynamic Range for the values in Sheet1 A1:A....
as
they will change in Row numbers daily - so I would want the code to
search
until the last value in Column A Sheet1.

I'm looking to place this code in a macro.

Thanks