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

Thanks again.  I have given feedback to MS, sorted a workaround as per my previous post so I guess this question is closed.  Thanks for all you help.

Answer 3

Just today I found a fix to this issue that works for me, although, the conditions which have to be observed might not work in your situation or in every situation.

I found that I can use multi-level subtotaling up to three levels and the groupings are correct, the totals are above the correct group, and the grand total stays at the top every time.  Here is how I did it (and it does have to do with particular conditions that are fooling Excel):

I have to copy my table of data to a new, completely blank sheet.  The header row must be in row 1; there can be nothing above the header row, not even a blank row.

Also, there cannot be any other use of the SUBTOTAL formula in the table at the time I run the Subtotal feature.

Due to the preferences of the Execs in my company I always use the Summary Rows above the groups, because of this I don't know if it would work with the summary rows below as your screenshot indicates.

After the subtotaling is done I can copy the whole table back to where I need it, but I simply wrote some script to do the formatting I require after the subtotals are applied.

NOTE:  I am using Excel 2013.  The data I'm using has almost zero formulas as it's a large extract from a database table.  There are a myriad of different situations that need to be tested or that might affect this functionality.  I just know that this bug has been around for years, and I've never found a workaround that actually works so I was very excited when I made this discovery.

I hope it works for you all as well.

Answer 4

Great that you found a solution and that you have shared it :).  I will try it sometime but my workaround is working still and indeed has some other advantages for my process.  

My workaround was: create the totals in a single, otherwise blank column,  delete the title of that column, copy, pastespecial, skip blanks to the columns where I want it).

Answer 5

I found a similar issue in Excel 2010 where multilevel subtotals when placed above the data do not place the totals line in the correct space.  This happens when one set of data only has one distinct combination between levels 1 and 2.  Its easily replicated, but I do not know if Microsoft is aware of it or has a hotfix for it.

See example below, where the data set Level 1 = 3 and Level 2 = 5.  When I added the 2nd level of subtotalling, since there is only one combination of 3-5, Excel doesn't know how to handle it and places the 2nd level subtotals in the wrong place.