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

This workbook shows the problems I am having.  I have moved all the code modules and placed it within a worksheet.  So copy the code to a new module.  There is a button on each sheet which will run the code and a messagebox to inform you if the subtotalling error has occured.

There are 4 datasheets - two of these trigger the issue and two do not.

SubTotsV2.xlsx

Thanks for any help.

Answer 3

I think it definitely has something to do with how data is laid out on the worksheets, but I haven't the time to try to clarify it further.  If it was just related to the number of subtotal "splits", it shouldn't work on any of them.  The problem also only occurs after the "second" subtotal (the one on column 59).

I'll try to look more later this week.  But with the sample workbook posted, others may also take a look and have some ideas.

Answer 4

Looking more closely, your problem seems to be discussed in a MSKB 831824 articleand may be related, in part, to a hot fix that was issued years ago.  However, I don't think it is quite the same since applying the fix in my Excel 2007 (which is covered in that article), still resulted in one of the Subtotals appearing at the wrong level, as you observe.

And you are using 2010 anyway

Answer 5

Yes, definitely Office 2010.

That hotfix also was limited to where you have totals above the data, which my example does not.  

It is also interesting that the trigger for my issue seems to be the number of  columns in which subtotals are applied as well as another undetermined thing to do with the shape of the data.

Is there any way I can alert MS to the bug so they can considering creating a fix?

I have applied a workaround involving:

• creating the subtotals in a single, otherwise blank column to the right of my data.
• deleting the title from that column, copying it
• looping through my array of column numbers using pastespecial, skip blanks to apply the formulae to all my columns.

Interestingly this approach does not seem to have adversely affected performance.  One would have thought that excel's in-built functionality would be quicker.

One of the things you recommended was to use a single cell reference (that then appears to be interpreted to currentregion) rather than my original approach (and MS's example) which specified the entire range explicitly.  Do you think that this was really necessary?