Moving Grouped Rows Based on One Cell Value

[Mastrius]

Hello,

I wasn't sure how to phrase this but here is my data:

ACCOUNT#|CUSTOMER|BATCH|ORDER#|ITEM_NAME|
123______|Customer1|*Blank*|1234 |Nail |
123______|Customer1|FA216 |1234 |Screw |
123______|Customer1|FB315 |1234 |Bolt |
[Blank Color-Filled Row ]
222______|Customer2|FA980 |4567 |Nut |
222______|Customer2|FB287 |4567 |Pedal |
222______|Customer2|FB983 |4567 |Hinge |
[Blank Color-Filled Row]

If there is a blank cell under the "BATCH" column, I need all rows associated with that Customer/Order# to be moved to a separate worksheet, and deleted from the existing sheet. So for example above, since Customer1's order has a *Blank* "BATCH" cell, all three rows of data that are associated with Customer1 need to be removed from this sheet, and inserted into a new sheet.

The column headers are always the same and will never change, but the number of rows (so number of orders, customers, etc.) change every day.

Any help would be greatly appreciated.

Thank you,
-Ryan

 

[mahhdy]

I think this would be helpfull:
[url]http://www.mrexcel.com/forum/excel-questions/980097-copy-row-condition.html#post4702453 [/URL]

Sub FilterMini()
With Range("A1").CurrentRegion
.AutoFilter Field:=3, Criteria1:="", Operator:=xlFilterValues  'if it is exactly "*Blank*" put it between the "" area
.SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")   'Edit sheet name   
.EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

Maybe works
Yours,
M

 

[mahhdy]

I checked it, It works perfect, But it will also copy the header row and cut it to the next page.
maybe one offset command can resolve it, I will try and let you know.
Just be mindful the code originally belongs to My Aswer Is This I just made a simple edit.
Yours,
M

 

[Mastrius]

Hello M,

Thank you for the response. I tried the code, and it copied the header row, as well as the single row that included the blank "Batch" cell. The only problem is that it did not copy the other two rows that were associated with that customer. So for the above example, I need it to copy all three rows associated with "Customer1" but it only copied the one row that had the blank batch when I tried it.

I also need this to scale with my orders, so that if there are multiple orders on the sheet that have this issue, it will copy all of them to the new sheet. But if I can get it working right with just one of the orders, I'm confident I can figure out the scalability issue myself.

Thanks,

-Ryan