Delete rows if value from cell is not in list
 

Hi,
I have a piece of code which deletes certain rows if an invoice number
is not on a list on another sheet.
I currently have to do this in 2 parts.

1. Insert a column next to the number I am looking up, and insert an
if statement that returns a 1 if the value is not in my other sheet.
Formula;
Range("D1:D100.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'All
Invoices'!C[-1],1,0)),1,"""")"

2. Select all the rows containing a 1, and delete them;
firstRowFound = True
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 6 To lastRow
If Range("D" & i) = 1 Then
If firstRowFound = True Then
Rows(i).Select
firstRowFound = False
Else
Union(Selection, Rows(i)).Select
End If
End If
Next i
Selection.Delete Shift:=xlShiftUp

This works ok.
But I want to know if I could select all the rows without have to
enter a new column, and the vlookup function.
Is there some sort of vlookup VBA statement that I can use, which will
have to same effect as vlookup??

Any help would be appreciated.
Thanks
Tony

Delete rows if value from cell is not in list
 

firstRowFound = True
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 6 To lastRow
If Not Iserror Application.Match(Range("D" & i), _
Worksheets('All Invoices').Columns(4),0)) Then
If firstRowFound = True Then
Rows(i).Select
firstRowFound = False
Else
Union(Selection, Rows(i)).Select
End If
End If
Next i
Selection.Delete Shift:=xlShiftUp


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bony_tony" wrote in message
ps.com...
Hi,
I have a piece of code which deletes certain rows if an invoice number
is not on a list on another sheet.
I currently have to do this in 2 parts.

1. Insert a column next to the number I am looking up, and insert an
if statement that returns a 1 if the value is not in my other sheet.
Formula;
Range("D1:D100.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'All
Invoices'!C[-1],1,0)),1,"""")"

2. Select all the rows containing a 1, and delete them;
firstRowFound = True
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 6 To lastRow
If Range("D" & i) = 1 Then
If firstRowFound = True Then
Rows(i).Select
firstRowFound = False
Else
Union(Selection, Rows(i)).Select
End If
End If
Next i
Selection.Delete Shift:=xlShiftUp

This works ok.
But I want to know if I could select all the rows without have to
enter a new column, and the vlookup function.
Is there some sort of vlookup VBA statement that I can use, which will
have to same effect as vlookup??

Any help would be appreciated.
Thanks
Tony