Subtotal Remove All command does not remove all Subtotals in Excel versions prior to 2010

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

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

  • KB2671038