Removeing the Grand Total Line when subtotals are removed

[Melody]

I have Excel 2003. I have a spreadsheet that I have run subtotals on.
However, when I do Remove All to remove the subtotals the grand total and the
+- bar is still there.

How can I get rid of all the totals in the spreadsheet including the grand
total and the +- bar on the left?

Thanks

 

[Dave Peterson]

I'd try removing those subtotals once more.

Make sure you select the entire range before you do it, though.

 

[Roger Govier]

Hi Melody

try
Data>Subtotals>Remove All

 

[melody]

I did that. It removed the subtotals but not the grand total at the bottom.

 

[Melody]

That worked. Got any idea how I can write that into a macro. I have the
remove all in the macro, just need to know how to make it select the whole
range to include the grand total line.

Thanks for your help.

 

[Dave Peterson]

Can you cheat and just use all the cells in the worksheet?

Worksheets("sheet1").Cells.RemoveSubtotal

 

[Dave Peterson]

ps. depending on what you're counting/summing/averaging..., you may want to try
data|pivottables. You may find them quicker to use and the reports nicer.

 

[Melody]

I agree, but unfortunately, I don't have any control over the content and
style of the spreadsheet. If I did, it would a lot more user friendly. I
will try your other suggestion, though and let you know how it goes. Thanks.

 

[Melody]

That didn't quite work, but this did. THANKS! These little things can be so
frustrating sometimes. Especially when you don't work with it daily.

Worksheets(1).Cells.RemoveSubtotal

 

[Dave Peterson]

I like to use the name of the sheet if I know it.

That didn't quite work, but this did. THANKS! These little things can be so
frustrating sometimes. Especially when you don't work with it daily.

Worksheets(1).Cells.RemoveSubtotal

 

[Melody]

DUH! That's what it didn't work. Oh well. In this case the macro is going
to be used on several different files so using the sheet number will work for
me.

Thanks again.

 

[Dave Peterson]

If you're cleaning up the sheet that's active, then maybe you'll want:

activesheet.cells.removesubtotal

DUH! That's what it didn't work. Oh well. In this case the macro is going
to be used on several different files so using the sheet number will work for
me.

Thanks again.