Subtotal by VBA

[K]

Hi all, I am trying to do subtotal with vba code. I created below
code to do this but when I run it I get error message saying "Run-time
error '1004' Application-defined or object-defined error". I think it
might relate to the code line below where it say
"TotalList:=Array(myary)". Because if i use "TotalList:=Array(9, 10,
11)" then it works fine but when i use "TotalList:=Array(myary)" it
gives me the error message which i mentioned above. I have to use
"TotalList:=Array(myary)" as columns in sheet can be less or more.
Please can any friend can help me on this.

************************Code******************************
Sub SubTotalRng()
Dim myary As String
R1 = Cells(1, Columns.Count).End(xlToLeft).Column
R2 = Range("A" & Rows.Count).End(xlUp).Row
For i = 9 To Cells(1, R1).Column
myary = myary & Str(i) & ","
Next i
myary = Left(myary, Len(myary) - 1)
Range(Range("A1:A" & R2), Range("A1", Cells(1, R1))).Subtotal
GroupBy:=1, Function:=xlSum, TotalList:=Array(myary), Replace:=True,
PageBreaks:=False, SummaryBelowData:=True
R2 = Range("A" & Rows.Count).End(xlUp).Row
Range(Range("A1:A" & R2), Range("A1", Cells(1, R1))).Subtotal
GroupBy:=4, Function:=xlSum, TotalList:=Array(myary), Replace:=False,
PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[Dave Peterson]

This creates a single string:
For i = 9 To Cells(1, R1).Column
myary = myary & Str(i) & ","
Next i


So
Array(myary)
is a single element array (that string).

You could use:

dim myAry() as long 'it's now an array
....

redim myAry(9 to cells(1,r1).column)
For i = 9 To Cells(1, R1).Column
myary(i)= i
Next i

delete that next line:
myary = Left(myary, Len(myary) - 1)


And don't use array(myAry), since myAry is already an array. Just use:
TotalList:=myary

 

[K]

Hi Dave, Thanks for replying. i am getting error on line "myary =
Left(myary, Len(myary) - 1) " Please can you let me have complete
code of your version. Many Thanks

 

[Dave Peterson]

Before I do that, read my original response again. Make sure you follow that
instruction about deleting the indicated line.

 

[K]

Before I do that, read my original response again.  Make sure you follow that
instruction about deleting the indicated line.

thanks its working

 

[Dave Peterson]

I knew you could do it!

thanks its working