SubTotal using VBA - How to exclude nested Subtotals in the range (subtotals in subtotals range adding up incorrrectlly)

Tesla

New Member
Joined
Jun 11, 2014
Messages
25
The customer had a very specific format for an automated report.
The report uses remote automation from MSAccess to pull from SQL Server then dynamically create around 400 various tabs.

The VBA Code takes 3 columns that are sorted.
Column 1 creates a Header on Top of each change - and a footer (with Subtotal) on bottom - then uses VBA to create group 1
So, the group1 is a list of all the groups with a subtotal.

Problem:
In column 2, the same process takes place. It inserts a Header on top of each group a Footer with Subtotals for each group change in column 2.
Now, the primary Subtotals from Column 1 are:
1. Including both details and SubTotal from group 1
2. Not including the count for the newly created rows (header 1 and subtotal footer row).

Perhaps the data can be collapsed back to group 1 and apply vba to only include the visible rows between the header and footer?
Or, is there a way for SubTotal formulas were suppose to ignore other Subtotal formulas in a range of cells?

The custom format prevents the use of the built in summing and grouping.

GroupA

..detail1 subtotal (in level1 the details and subtotals for these are hidden)
..detail2 subtotal
..detail3 subtotal
SubTotal GroupA
GroupB
..detail1 (in level1 the details and subtotals for these are hidden)
..detail2
.....Detail2 (shown in Level2)
.....Joe
.....Fred
.....Detail 2 Subtotal
..detail3
SubTotal GroupB
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Tesla

New Member
Joined
Jun 11, 2014
Messages
25
Apologies, the Subtotal(9,Range) does not re-add the other sub-subtotals.
However, the sub-subtotal inserts did throw off the rollup outer Sub-totals range.
Had hoped an Insert row would have auto-incremented the formula's range.
Now, to figure out a way to pick the level one (outside) view and redo the GroupA ... GroupB Subtotal ranges.
 

Tesla

New Member
Joined
Jun 11, 2014
Messages
25
Now, there is really egg on my face. The big subtotals were created by loops. After completing some hard code, I forgot to increment the loop counter for the header / subtotal footer.
So, all of the outer Subtotals did in fact grow dynamically. It all works.

My final product mimicks a Tree Directory view with 4 levels. The 1 level doesn't just show the grand total, it shows the major categories with subtotals.
Now, this can also be copied with VBA to create an executive overview in Power Point
 

Watch MrExcel Video

Forum statistics

Threads
1,122,487
Messages
5,596,450
Members
414,068
Latest member
FAH

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
Top