Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Excel 2010 vba break logic for nested subtotals

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
dkcoop03
Asked:
dkcoop03
  • 3
  • 2
1 Solution
 
aikimarkCommented:
Have you tried the built in sub-totals feature?
0
 
dkcoop03Author Commented:
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
 
aikimarkCommented:
Please take a look at this.
testreportsubtotals.xlsm
0
 
dkcoop03Author Commented:
So it looks like all you did was uncheck the box to add subtotals at the bottom on the first subtotal.  Thanks!
0
 
aikimarkCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now