Sum After Segmented Number Series in Column

Gary

New Member
Joined
Feb 21, 2002
Messages
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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