Multi-level subtotals in Excel 1

[Gavona]

I am using vba to sort and then apply subtotals (along with sytles and formats for the subtotal rows). I have come across a bug in excel that appears to mean I have to start again without the subtotal method. (I have the impression that the precise bug seems to differ between excel versions though I have not tested this aspect extensively.)

This is going to be part of a spreadsheet used by top management. They are going to insert new records (through press of a button), change existing data (with data validation), sort and refresh the sub-totals by press of a button. They have Excel 2003 sp2 or Excel 2000. I am testing on Excel 2003 SP3.
The problem can be seen by putting this simplified data into your spreadsheet:

Field1 Field2 Amount
North banana 10
North banana 11
North apple 15
South apple 15
South apple 17
South apple 10
South [red]apple[/red] 11
East banana 15
East banana 15
East apple 11
East apple 15
East apple 15

And applying this code (or using sub-totals manually).

Sub TestSubtot()
With Selection
    .CurrentRegion.Select
    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    
    .CurrentRegion.Select
    .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

Select any cell within the data within the data then run the macro.

Change the red cell to banana and run the macro again. Works much better if the data is like this.

I am surprised not to find loads of stuff about this on the web. I thought I would find plenty of references and solutions.

So, unless you know better then I need to rapidly find or develop code to replicate the subtotal wizard without the bugs. Any ideas, hints etc?




Gavin

 

[PHV]

Works for me (xl2003sp3) with both apple or banana.

 

[SkipVought]

Gavin,

Assuming your table starts in A1...

Sub TestSubtot()
    With [A1]
        
        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
        .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
            Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    End With
End Sub

work with changing apple to banana and vis versa.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Hmm, please could you examine carefully the results. I believe that some of the subordinate field2 subtotals appear after the Field1 subtotal to which they relate. This behavior does seem vary with excel version and, in my real data, things can get very inconsistent in some versions of excel.
I won't get a chance to come back to the site 'til the end of the (UK) day so apologies if I am slow to respond.
If you can't see the issue then I will post a spreadsheet with the examples.

Thanks for looking,

Gavin

 

[xlbo]

Hmmm - the error I see is that on the 1st pass, the EAST subtotal does not appear in the group 2 outline - it only appears at group 3 (whereas North / South appear in group 2)

On the 2nd pass (with banana rather than apple), EAST does appear in the group 2 outline

Is this what you see Gavin?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Yes Geoff, you have got it! Futhermore in other examples the confusion over levels works it way down the workbook. So that changes in Field2 become superior to changes in Field1.

I cannot post a sample file to show this from work (blocked)....

Thanks,

Gavin

 

[Gavona]

So, we have established that there is a problem with excel's subtotalling functionality. An annotated example is now attached to illustrate it more clearly.

1. Any ideas about the best approach to solving my problem / overcoming the limitations of the inbuilt functionality?

2.How would I best report this to MS?

3.(I would also be delighted with any comments as to how I could improve the VBA within the attached example - but that is not really the point of this post)

In anticipation,

Gavin

 

[Gavona]

I just stumbled across this that seems to recognise the issue I had and provides a registry fix. I have not tested at all.

It appears that a fix for a different issue, distributed with Microsoft Office 2003 Service Pack 1 (SP1) introduces the problem

http://support.microsoft.com/default.aspx?scid=kb;en-us;831824

The fix is enabled by default when you install the update. However, it may cause incorrect ordering of subtotals when the following conditions are true:

* The summary appears below the detail data.
* One or more subtotal breaks contain only a single item.

Follow these steps to disable this hotfix and to resolve this issue by reverting to the subtotal functionality of Excel 97 and Excel 2000

(It will not solve the issue for me as the fix would need to be applied to any pc on which the spreadsheet was used - and this is for a spreadsheet shared with many.)

Gavin

 

[JoeyLoving]

Try using sumif, set criteria to field 1