Issue with nested data subtotals

[jday]

I have a worksheet range containing attributes in columns A, B, and C -- with
data to be subtotaled in column D. The attribute columns are sorted so they
are grouped together appropriately left to right. I want to insert subtotals
both at column A and B -- with a being the primary group, and subtotals for
column B nested inside that. I also want, in both cases, for the actual
subtotal row to be ABOVE each grouping vs. below. Here is the code I am
using:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=False

The problem happens when it creates/inserts the subtotals for the 2nd
iteration in column B. In each case, the first 'subtotal' row for column B
appears ABOVE the subtotal row in column A, and in some cases, the last
subtotal row for column B is duplicated with both a subotal at the top &
bottom. If I edit the code to allow the summary data to fall BELOW the
group, it summarizes everything correctly. In other words, it is my attempt
to keep each summary at the top of each group that seems to be causing my
issue---but this is what I REALLY want. By the way, I cannot use a pivot
table due to other things I will be doing with this data. Any suggestions on
what I can do -- or is there something wrong with my code that I am missing?

 

[ryguy7272]

Not sure of your exact setup, but I put 'Group' in A1 and 'Amount' in B1. I
have A, B, C in ColumnA and some randome numbers in ColumnB. Data >
Subtotals > Uncheck the Summary Below Data box, and OK. Now I have all
Subtotals ABOVE the Numbers, with the Grand Total in Cell B2. Not sure if it
helps, but here is the recorded code:

Sub Macro1()
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
End Sub

HTH,
Ryan---

 

[jday]

There is no problem when you only need one row subtotaled. My problem is
that I need to insert ONE subtotal using column A as the group, then a SECOND
subtotal (nested) using column B as the group. For example, here is what the
data might look like in columns A-C:

Brand SubBrand Volume
------- ------------ ----------
Brand1 AAA 100
Brand1 BBB 200
Brand1 BBB 300
Brand1 CCC 200
Brand1 CCC 100
Brand2 XXX 200
Brand2 XXX 300
Brand2 ZZZ 400

I want to insert one subtotal at the Brand level, and another at the
SubBrand level. I have no problem understanding the steps to perform this
action---the problem is that some of the subtotals for SubBrand actually
appear ABOVE the line for the Brand total when all is said and done. Can't
understand why and no matter how I arrange the data, the result is the same!!
ARRGGHH!! Driving me nuts

 

[ryguy7272]

That seems weird to arrange the data that way. I guess you tried a pivot
table already and it didn't work for you? I tied it and at least it made
sense...to me anyway.

I put Brand in rows and SubBrand just to the right of it, then added Volume
to data. Then swapped the SubBrand and Brand; seems ok to me. You have some
Subtotal options in the pivot table. I'd recommend exploring those options
and see if you can get the results you need.

HTH,
Ryan---