Moving Grouped Rows Based on One Cell Value

Mastrius

New Member
Joined
Dec 12, 2016
Messages
2
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I think this would be helpfull:
[url]http://www.mrexcel.com/forum/excel-questions/980097-copy-row-condition.html#post4702453 [/URL]

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,503
Members
449,945
Latest member
noone12344444444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top