C
Cooz
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
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