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("D1100.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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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("D1100.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
>