Subtotal by VBA


K

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
 
Ad

Advertisements

D

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

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
 
D

Dave Peterson

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

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtotal Formatting 2
Excel Subtotals 2
Subtotal for same range of multiple worksheets 5
Subtotal function 2
Issue with nested data subtotals 3
Subtotals 2
Subtotal macro in each worksheet 13
subtotal question 1

Top