Excel 2010 Multi-Level Subtotals create outline in wrong position

Question

I am using 2 levels of subtotals applied with VBA.   I get to this in two stages - the first stage works fine:

1. 

Then, I re-apply subtotals to the range "$A$6:$BG$27", grouping by Column BG.  Row 24 is bold because there is a change in column BG so rows 24-25 should be within a level 3 sub-total group.

 

 The 144009 Agency Worker Total should be at Outline level 2 (like the 244009 one), not 3 as in the picture  and should be on row 29.  Row 29 actually contains the formulae subtotalling rows 26 and 27.

Now this does not happen on all the worksheets I apply the approach to - so there must be particular conditions that fool Excel's functionality.  (I seem to remember that this was a bug in Excel 2003 too).   Any suggestions - links to hotfixes etc would be welcomed.

Answer 1

The hotfix corrected that problem, but introduced a new one which seemed similar to (but not identical with) what you are experiencing.  And since the hotfix was effectively applied to 2002-2007, the reversal of the hotfix might have been relevant.  But it did not work in 2007 :-(

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.

With regard to selecting a single cell, in a related article regarding 2007, again, for something that is similar but not identical, MS recommends selecting only a single cell.  But I don't think it is absolutely necessary.  In most cases, I think it is simpler.

In your case, you could also detect the table by something like (untested)

Set R = [a6].currentregion

set R = R.Resize(rowsize:=r.rows.count-1)

In doing some testing, however, I noted that, when using your original table and the "long" array that did not work; using the "single cell" method and NOT removing that "end of data" line, the result is that the second level subtotals all wind up at level 2, as desired; but the "end of data" line is duplicated, with the Grand Total showing below it.  I don't know if that is useful information, but it is interesting.

Here are some screen shots of that phenomenon, on sheet CC3 of the sample file.  (Ignore the column with the Red interior; I was coloring the subarray columns to see if there was anything there that would provide a clue.

Expanded View:

Collapsed to Level 2 subtotals only

With regard to reporting this, there is a mechanism, but I am not aware of the link.  There is a link here for feedback, but I don't know if it goes to the development team or not.  I would suggest an internet search for "reporting bugs to microsoft Excel 2010" or similar.

Answer 2

Almost impossible to find your problem without code and realistic data.  Your screenshots do not seem to show any difference in the Cost Element column.

If you cannot share code and data, I would suggest first doing this manually, using the macro recorder to see the code that is generated; then compare that with your own VBA code to try to understand the problem.

Do you need to do it in stages?  If you do, you might try removing the pre-existing subtotals, and combine the operation on the first and second stage.

Answer 3

....Your screenshots do not seem to show any difference in the Cost Element column.

There is indeed no difference in the Cost Element Column.  The last two data records in screen shot1 have a different value to the other rows in column BG (which I don't show and is used for the next level of sub-totals).  But you can tell something is wrong in the second screenshot.  Rows 26 and 27 should have their own subtotal before the Cost Element Total.  Instead it comes after.

I can replicate manually. 

"Do you need to do it in stages?  If you do, you might try removing the pre-existing subtotals, and combine the operation on the first and second stage."    I don't think you can achieve multilevel subtotals in a single step. :(

My code simply applies subtotals twice because that is (my understanding of) the way one applies multilevel subtotals in excel. The subtotal method is applied twice in quick succession.  The actual code looks complex but effectively is:

Range("A6:BGxx").Subtotal  GroupBy:=1, Function:=xlSum, TotalList:=Array(.....)

[==>> results as in first screenshot]

Range("$A$6:$BG$27").SubtotalGroupBy:=34, Function:=xlSum, TotalList:=Array(.....)

[==>> results as in second screenshot]

Answer 4

The remaining arguments to the Subtotal method that you don't show are important, as might be the data that you are working on.  And, unless you need to inspect the data after each subtotal application, I don't see any reason to do it the way you have.

Again, assuming the arguments you are not showing are specified properly, I would suggest something like:

With Range("A6") '<--note the single cell reference

     .Subtotal  GroupBy:=1, Function:=xlSum, TotalList:=Array(.....)

     .Subtotal  GroupBy:=34, Function:=xlSum, TotalList:=Array(.....)

end with

I cannot reproduce your problem given the information you have provided.  And I doubt I will be able to help you further without it.  I would suggest, if you need further help, to post a copy of a set of data and your macro demonstrating the problem on some public file sharing site, such as OneDrive, or Dropbox, or any of a number of others; and post a link here.

Answer 5

Hmm this has been most frustrating - thanks for sticking with it.  I have now determined one of the trigger for the error.  The error will be reliably reproduced on certain of my sheets if my TotalList array has more than 23 elements!

However, even then it only fails on some sheets - so the shape of the data also has an impact though I haven't been able to determine what exactly that trigger is.

Sub testsubtots()

SCFUnprotect

'Call RemoveReApplySubTots(Array(5, 12, 13, 14, 16, 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29, 30, 31, 34, 36, 38, 39, 40, 41, 42, 43, 44, 45, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58))

'Call RemoveReApplySubTots(Array(17, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29, 30, 31, 34, 36, 38, 39, 40, 41, 42, 43, 44, 45)) ' 23 elements - works

'Call RemoveReApplySubTots(Array(5, 30, 31, 34, 36, 38, 39, 40, 41, 42, 43, 44, 45, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58)) '23 elements - works

Call RemoveReApplySubTots(Array(5, 12, 30, 31, 34, 36, 38, 39, 40, 41, 42, 43, 44, 45, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58)) '24 elements - generates error

End Sub

My data block unfortunately has data on the rows immediately before and after the block.  So to move to your recommendation of specifying a single cell I am inserting rows before and after it.

Sub RemoveReApplySubTots(subarray)

Dim r As Range

Application.EnableEvents = False

'First we make sure data area is surrounded by blank rows

'we will delete these rows at the end of this sub

Range("TitleRow").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("EndRow").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("EndRow").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("EndRow").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Range("TitleRow").Cells(1, 1)

    Application.DisplayAlerts = False 'don't show prompt that entire row will be removed

    .RemoveSubtotal                        'should be unnecessary with this method

    Application.DisplayAlerts = True

    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(subarray), _

        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    .Subtotal GroupBy:=59, Function:=xlSum, TotalList:=Array(subarray), _

        Replace:=False, PageBreaks:=False, SummaryBelowData:=True

End With

Range("TitleRow").Offset(-1).EntireRow.Delete

Range("EndRow").Offset(-1).EntireRow.Delete

Range("EndRow").Offset(-1).EntireRow.Delete

Range("EndRow").Offset(-1).EntireRow.Delete

End Sub

I will try to create some non-confidential sample data to illustrate the issue.