Testing for subtotal?

[tourless]

Hi Folks,

I have a routine that takes a bunch of sheets in an array and performs a few actions including removing a subtotal. But what I'm noticing is if the active sheet in the loop does not have a subtotal I get 'RemoveSubtotal method of Range of class failed' error. Is it possible for first test for the existence of a subtotal and remove it only if it does exist? Here's what I'm working with... yeah I know about selecting...

 'Remove previous results
    For Each ResultsSh In Sheets(Array("HT-LB-W1V", "COG-M-LB-W1V", "COG-CMPS-LB-W1V", "Revenue", "Revenue2"))
        'ResultsSh.Select
        'Clear old result set from array
        ResultsSh.Rows.Hidden = False
        ResultsSh.Columns("A:B").Delete Shift:=xlToLeft
        ResultsSh.Range("A1").RemoveSubtotal
        'ResultsSh.Range("A1").Select
    Next

Thanks.

 

[ParamRay]

Replace the RemoveSubtotal line with:

Range("A1").ListObject.ShowTotals = False

 

[tourless]

Replace the RemoveSubtotal line with:

Range("A1").ListObject.ShowTotals = False

that throws 'object variable of with block variable not set'.

 

[ParamRay]

that throws 'object variable of with block variable not set'.

Are we talking about the total row of a table here, and is the top-left of the table always in cell A1 ?

 

[tourless]

Are we talking about the total row of a table here, and is the top-left of the table always in cell A1 ?

Yes we are, and yes it is.

 

[ParamRay]

Yes we are, and yes it is.

Ah, did you prefix the line of code with ResultsSh, as in:

ResultsSh.Range("A1").ListObject.ShowTotals = False

My bad if you didn't. If you did though, I can see no reason why you should get an error...

 

[tourless]

yep, my bad.