Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do not delete rows in a list. | Excel Programming | |||
Delete empty rows in list of data | Excel Programming | |||
Delete rows that do not contain a list of values | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete a list of rows | Excel Programming |