Solved

Excel 2010 vba break logic for nested subtotals

Posted on 2013-01-30
5
559 Views
Last Modified: 2013-01-31
I am working with an Excel spreadsheet that needs nested subtotals.  I know I can do this in a pivotTable but most of the code to process this worksheet is already done.  I just need the subtotal part.  I used the subtotal function but it doesn't give me what I want.  I am attaching a copy of what I have now and what I want.  I want the Main subtotal to show up above the groupings and I want the nested subtotal to show up below the groupings.  So I think I will need to loop through each of the two ranges, compare one row to another and determine if they are the same or not.  If they are the same then I would accumulate the totals.  If they are not then I would put out the subtotal.  I'm not exactly sure of the best way to do this in code.  Any help would be appreciated.
testreportsubtotals.xlsm
0
Comment
Question by:dkcoop03
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Have you tried the built in sub-totals feature?
0
 

Author Comment

by:dkcoop03
Comment Utility
Yes I have, it puts all subtotals at the bottom, but otherwise it works.  I figure since I'm going to have to count rows to put the main subtotal on top anyway, I might as well do the subtotals in code.  But if you have a better way please let me know.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
Please take a look at this.
testreportsubtotals.xlsm
0
 

Author Closing Comment

by:dkcoop03
Comment Utility
So it looks like all you did was uncheck the box to add subtotals at the bottom on the first subtotal.  Thanks!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I think I did the following:
* reordered the columns
* added two subtotals
* deleted the GrandTotal row
* clicked on the next to right-most grouping button, hiding the detail rows
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now