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

Article ID: 2671038 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

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.

RESOLUTION

This issue is fixed in Excel 2010.

MORE INFORMATION

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.
ActiveSheet.Cells.RemoveSubtotals
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.

ActiveSheet.Rows("5:1000").RemoveSubtotals
For additional information on referencing specifc ranges in Excel.
291308 How to select cells/ranges by using Visual Basic procedures in Excel


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.

Properties

Article ID: 2671038 - Last Review: January 30, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • Microsoft Office Excel 2003
Keywords: 
KB2671038

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com