Unanswered: Parse through records, deleting based on criteria...
A query returns a spreadsheet containing Work Order information. I want to exlude Items that have been sent outside for machining, but only if the machining was performed in the 2nd operation. I have attached a sample spreadsheet, as the results of this query are best described by example.
the last column contains the Operation Type (OPTYPE). If the second line of a work order has "OUT" for the OPTYPE, I want to delete that record: Work Order 55158 has OPTYPE=OUT in the second row (or second op, row 15) for that work order - I wan't to delete this work order (delete rows 14, 15, 16). Work Order 56947 would not be deleted because the "OUT" Operation is in the 4th line (or 4th operation).
after deleting these work orders, I want to go through and delete all of the blank rows after a work order (rows 3-4, 6-9, 11-13, etc.).
Can this be done with a macro? Any idea's on how to at least get started? I have some limited VB experience. Any help is appreciated!
Im going to expand on what sugarflux has said.
To find the order out in each sucssive row afte the initial work order then just do he following.
Range("G2").select ' the first row with a work order in it
do until activecell.row > 65000 ' this indicates the end of file
if activecell.offset(1,1).formula = "OUT" then
'to clear all the rows after the inital order line
dim Lastrow as Double
dim MyRange as String
lastrow = range("H1").end(xldown).row ' determine the lastrow with data
MyRange = "A1:A" & Lastrow 'select the first column down to the lastrow without data
just copy and paste the abovecode into a module
and it should work quickly