Sum After Segmented Number Series in Column

Gary · Feb 22nd, 2002, 11:13 AM

Hello,

I hope someone can help me or point me in the right direction. I have a file that has three blank rows between each different storeroom number. I need to add totals to the second (middle) blank cell after every segmented number series under QTY, PRICE, and TOTAL; however, I need to have the flexibility to be able to pick a starting cell in any column, not just column B, C, and D (this is just one file we would use this for).

STORE
ROOM.....QTY......PRICE......TOTAL
005-29........1........33.00......33.00

Need total>...1........33.00.....33.00

005-2921......1........73.64.....73.64
005-2921......1........73.64.....73.64
005-2921......1........73.64.....73.64

Need total>...3.......220.92....220.92

020-1065......2.........4.06......8.12
020-1065......6.........4.06.....24.36

Need total>...8.........8.06.....32.48

Is there a macro that would do this? I've searched, but did not find one. I appreciate any assistance, thanks.

Anne Troy · Feb 22nd, 2002, 11:22 AM

Select your first column. Hit Edit-Go to-Special. Choose Blanks, hit OK. Now hit Edit-Delete and choose Entire row. That deletes all your blank rows. Now, sort by your store room number. Hit Data-Subtotals. Tell it For each change in Store number to give you the SUM of whatever columns you want. Hit ok. At the upper-left of your screen, you'll see a 1-2-3 (just above the row numbers). Hit the 2.

Gary · Feb 23rd, 2002, 05:39 PM

Thank you for your suggestion, but I was wondering if there was a VBA way to click a start cell in a column (column is variable), put the subtotal in the middle blank cell below each segment, and then return to the starting cell without changing the format of the sheet and without having to deal with the "total" and "grand total" lines created by the subtotal command. I hope this makes sense.

Feb 22nd, 2002, 11:22 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Select your first column. Hit Edit-Go to-Special. Choose Blanks, hit OK. Now hit Edit-Delete and choose Entire row. That deletes all your blank rows. Now, sort by your store room number. Hit Data-Subtotals. Tell it For each change in Store number to give you the SUM of whatever columns you want. Hit ok. At the upper-left of your screen, you'll see a 1-2-3 (just above the row numbers). Hit the 2. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 22nd, 2002, 11:13 AM	#1
Gary New Member Join Date: Feb 2002 Location: Paducah, KY Posts: 3	Hello, I hope someone can help me or point me in the right direction. I have a file that has three blank rows between each different storeroom number. I need to add totals to the second (middle) blank cell after every segmented number series under QTY, PRICE, and TOTAL; however, I need to have the flexibility to be able to pick a starting cell in any column, not just column B, C, and D (this is just one file we would use this for). STORE ROOM.....QTY......PRICE......TOTAL 005-29........1........33.00......33.00 Need total>...1........33.00.....33.00 005-2921......1........73.64.....73.64 005-2921......1........73.64.....73.64 005-2921......1........73.64.....73.64 Need total>...3.......220.92....220.92 020-1065......2.........4.06......8.12 020-1065......6.........4.06.....24.36 Need total>...8.........8.06.....32.48 Is there a macro that would do this? I've searched, but did not find one. I appreciate any assistance, thanks.

Feb 23rd, 2002, 05:39 PM	#3
Gary New Member Join Date: Feb 2002 Location: Paducah, KY Posts: 3	Thank you for your suggestion, but I was wondering if there was a VBA way to click a start cell in a column (column is variable), put the subtotal in the middle blank cell below each segment, and then return to the starting cell without changing the format of the sheet and without having to deal with the "total" and "grand total" lines created by the subtotal command. I hope this makes sense.