MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Remove subtotals problem


Posted by A.R. on January 30, 2002 7:14 AM

I receive worksheets that include subtotal data. I have to copy this data to another worksheet showing all the data lined up without the subtotals and blank rows. The "Subtotal-Romove All" feature works for some worksheets but not for all of them. Why won't the "Subtotal-Romove All" feature remove subtotals from some worksheets? Any advise, tips? Otherwise I would have to manually delete the subtotals one by one.


Posted by Mark W. on January 30, 2002 7:21 AM

When you pasted onto your other worksheet did
you use Paste | Special... Values? If so, you
replaced the SUBTOTAL worksheet functions with
their resultant values. Without these functions
the SUBTOTAL facility won't recognize this list
as having subtotals.

Posted by Juan Pablo G. on January 30, 2002 7:24 AM

That problem arises, I believe, when the subtotal area is not *obvious* for Excel.

Juan Pablo G.

Posted by A.R. on January 30, 2002 9:06 AM

Message for Mark W.

No, I do a "Subtotal-Remove All" feature on the original worksheet and then do a Copy-Paste-Special to a new worksheet.


Posted by Mark W. on January 30, 2002 11:03 AM

Re: Message for Mark W.

Is it possible that someone performed a
Copy/Paste Special... Values on the "original"
worksheet(s)? Do the listings that you can't
remove subtotals have any formulas?


Posted by A.R. on January 30, 2002 12:23 PM

Re: Message for Mark W.

Yes, the listings that I can't remove subtotals do have formulas. Is it possible that someone performed a


Posted by Mark W. on January 30, 2002 12:30 PM

Do they include SUBTOTAL functions? [nt]


Posted by A.R. on January 30, 2002 12:47 PM

Re: Do they include SUBTOTAL functions? [nt]

I don’t know if they include Subtotal functions. How do I find out? : Yes, the listings that I can't remove subtotals do have formulas.


Posted by Mark W. on January 30, 2002 4:50 PM

Re: Do they include SUBTOTAL functions? [nt]

Select the cells that contain subtotal values
and look at the formula bar. Does the formula
bar look like =SUBTOTAL(... ? I don’t know if they include Subtotal functions. How do I find out?


Posted by A.R. on January 31, 2002 8:28 AM

Re: Do they include SUBTOTAL functions? [nt]

1. How do you claculate two business days from another date, and excluding holidays?
2. Also, how do you subtract one date from another and show the anwer in number form and in business days,and excluding holidays?
Example 1: Date Sent = 1/18/02 and Expected Received Date = +2 business days/exclude holiday = 1/24/02.
Example 2: Date Received 1/24/02 - Date Sent 1/18/02 = 2.


Posted by A.R. on January 31, 2002 8:32 AM

Ignore message above-Re: Do they include SUBTOTAL functions? [nt]


No, there is no =SUBTOTAL(…) in the formula bar. This explains why the Subtotal-Remove All feature will not work. The worksheets where the Subtotal-Remove All" feature does work does have the =SUBTOTAL(…) in the formula bar. I guess the only way to remove the subtotals is by deleting the subtotals amount manually one by one. Too bad because the Subtotal-Remove All feature only takes seconds. You have been very helpful, thank you.

A.R.

Select the cells that contain subtotal values