Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel 2010 vba break logic for nested subtotals

Posted on 2013-01-30
Medium Priority
591 Views
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
Question by:dkcoop03
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 46

Expert Comment

ID: 38838182
Have you tried the built in sub-totals feature?
0

Author Comment

ID: 38839995
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 46

Accepted Solution

aikimark earned 2000 total points
ID: 38840213
Please take a look at this.
testreportsubtotals.xlsm
0

Author Closing Comment

ID: 38840242
So it looks like all you did was uncheck the box to add subtotals at the bottom on the first subtotal.  Thanks!
0

LVL 46

Expert Comment

ID: 38840479
I think I did the following:
* reordered the columns
* deleted the GrandTotal row
* clicked on the next to right-most grouping button, hiding the detail rows
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll