Article ID: 2671038 - View products that this article applies to.
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.
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.
(http://support.microsoft.com/kb/291308/[anySimpleType]/ )How to select cells/ranges by using Visual Basic procedures in Excel
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2671038 - Last Review: January 30, 2012 - Revision: 2.0