Error 1004 - this can't be applied to selected range

[paulr1680]

Following code is used to set up subtotals:-

Range("A1").Select
rowcount = Selection.CurrentRegion.Rows.Count
colcount = Selection.CurrentRegion.Columns.Count
Range(Range("A1"), Range("A1").Offset(0, colcount - 1)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

This sometimes, but not always, fails "Error 1004: This can't be applied to the selected range. Select a single cell in a range and try again"

If a single cell is selected, current region is assumed, so I commented out the "Range(Range("A1"), Range("A1").Offset(0, colcount - 1)).Select" line

In this case one test that had previously failed now worked; however another test still failed.

Any suggestions?
Is the intermittent failure because of the amount of data?

Thanks in anticipation

Paul

 

[jasonb75]

You're applying subtotals to the 8th and 9th columns in the selection / region as dictated by TotalList:=Array(8, 9)
The number of columns in the selection / region can not be less that the highest number in the array.

 

[paulr1680]

You're applying subtotals to the 8th and 9th columns in the selection / region as dictated by TotalList:=Array(8, 9)
The number of columns in the selection / region can not be less that the highest number in the array.

Thanks Jason, but I'm afraid that isn't the problem - the region extends to column 46
Paul

 

[jasonb75]

There is nothing else there that would cause an error unless the sheet is protected.

What does

MsgBox Range("A1").CurrentRegion.Address(0, 0)

tell you?