Hi everyone,

Is there any function in VBA that can determine whether subtotals are
displayed on a particular sheet?
Something like aSheet.HasSubtotals that returns True or False would be nice.
It doesn't exist, though.

In addition, a macro of mine contains the following code, that cleans up a
certain worksheet before filling the sheet with new data:

intOutputRij = 4
Set shtOutput = ActiveWorkbook.Sheets("Output")
With shtOutput.Range("A" & Format(intOutputRij) & ":IV65536")
.ClearContents ' remove old data
.Font.Bold = False
.Range("A:A").RemoveSubtotal ' error 1004 when there's no subtotal
End With

Error 1004 is trapped with a Resume Next. The RemoveSubtotal method doesn't
work as nicely as it should. Sometimes I do get a clean worksheet, but
sometimes it leaves the subtotals untouched, resulting in about 7 or more
levels to collapse/expand, or in a movement down, allowing me to
collapse/expand empty rows.

Any suggestions?

Thank you,
Cooz

or just remove the outline to clean data

With Range("A:A").Select
Selection.ClearOutline
End With


On Oct 19, 1:59*pm, Cooz wrote:
Hi everyone,

Is there any function in VBA that can determine whether subtotals are
displayed on a particular sheet?
Something like aSheet.HasSubtotals that returns True or False would be nice.
It doesn't exist, though.

In addition, a macro of mine contains the following code, that cleans up a
certain worksheet before filling the sheet with new data:

* * intOutputRij = 4
* * Set shtOutput = ActiveWorkbook.Sheets("Output")
* * With shtOutput.Range("A" & Format(intOutputRij) & ":IV65536")
* * * * .ClearContents ' remove old data
* * * * .Font.Bold = False
* * * * .Range("A:A").RemoveSubtotal ' error 1004 when there's no subtotal
* * End With

Error 1004 is trapped with a Resume Next. The RemoveSubtotal method doesn't
work as nicely as it should. Sometimes I do get a clean worksheet, but
sometimes it leaves the subtotals untouched, resulting in about 7 or more
levels to collapse/expand, or in a movement down, allowing me to
collapse/expand empty rows.

Any suggestions?

Thank you,
Cooz