Subtotal Remove All command does not remove all Subtotals in Excel versions prior to 2010
When you use the Subtotal dialog's Remove All button or the Range.RemoveSubtotals method not all rows with a SUBTOTAL function in them are removed.
This issue is fixed in Excel 2010.
If the SUBTOTAL function has a nested function Excel may not remove the row.
In Excel 2010 existing code solutions using the RemoveSubtotal method may remove rows that were previously skipped. If developers want the rows to remain they should adjust the Range object that the RemoveSubtotal method is applied to so that it only includes the range which contains the SubTotals.
For Example the following code applies to all of the cells in a worksheet so any row containing a SUBTOTAL function will be removed.
You can adjust that to only remove rows in the area of the worksheet that excludes the rows you want to keep, for instance if the rows at the top of the worksheet contain SUBTOTAL functions that you want to keep, you can adjust the code to apply to specific rows. For example the SubTotaled rows are within the range of rows 5 to 1000.
For additional information on referencing specifc ranges in Excel.
291308 How to select cells/ranges by using Visual Basic procedures in Excel
subtotal vba removesubtotals xl2010 function
Article ID: 2671038 - Last Review: 01/30/2012 20:43:00 - Revision: 2.0
Microsoft Office Excel 2007, Microsoft Excel 2010, Microsoft Office Excel 2003