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!

Thanks,
Keith

I would use a Do...Loop statement.

For example:

Dim myRow as Integer

myRow = 1

Do
myRow = myRow + 1
If Cells(1, 8).Text = "OUT" Then


Loop Until myRow = 1000 'number of rows you want to check

I would use a Do...Loop statement.

For example:

Dim myRow as Integer

myRow = 1

Do
myRow = myRow + 1
If Cells(1, 8).Text = "OUT" Then
Cells(1, 8).EntireRow.Delete
End If
Loop Until myRow = 1000 'number of rows you want to check

This gives the general code for you to play with and you can work out the specifics yourself.

sugarflux

PS. I accidentally pressed Tab and enter when i was half way through my response so ignore the previous reply!

Keith

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
activecell.entirerow.delete
end if
activecell.end(xldown).select
loop

'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
range(myrange).specialcells(xlcelltypeblanks).enti rerow.delete
Range("A1").select

just copy and paste the abovecode into a module
and it should work quickly

Hope This is ok for you
David

Thanks!

Your suggestions have been very helpful.