Dynamic Subtotal VBA Help

[BRich83]

I'm trying to build a macro to dynamically choose which columns to display a subtotal when using the subtotal function. It looks like it just uses an array for the list that's normally in the Subtotal Wizard. Here's the code I've attempted.

Sub Subtotal_Create()

Dim ARString As String

'Creates subtotals for sheet
ARString = "8,9,10,11,12"
For i = 13 To lastcol
ARString = ARString & "," & i
Next i

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(ARString), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

End Sub


I always want columns 8:12 but the rest may be shorter or longer depending on the data I'm importing. Is there a way to get that string into the command like I'm attempting or is there another, easier way to do it?

 

[Nalani]

First off I see that lastcol is not Dimensioned nor Set, maybe:

Dim lastcol As Integer
lastcol = sht.Cells(1, 255).End(xlToLeft).Column

Then with this > ARString = ARString & "," & i

I could be wrong here, but once i is inserted into the Array, ARString will look like this > ARString = "8,9,10,11,12",13 which will throw an error.

I'm not sure how to fix that, but I'm sure sombody else will chime in to help you (and me) on that.

 

[BRich83]

No, that's not my problem. Sorry I didn't explain it too well, but lastcol is already defined elsewhere. This is a function that's called in a macro.

Here's what I've tried:

Sub Subtotal_Create()
'Creates subtotals for sheet
ARString = "8, 9, 10, 11, 12"
For i = 13 To lastcol
ARString = ARString & ", " & i
Next i
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(ARSTring), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Columns("A:A").EntireColumn.AutoFit
End Sub

Here's what I want it to be doing:
Sub Subtotal_Create()
'Creates subtotals for sheet
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
Columns("A:A").EntireColumn.AutoFit
End Sub

But I want the array list to be dynamically created. Basically it is creating a subtotal for a sheet that has daily time entered. However there will be some weeks that won't necessarily be the same (i.e. the beginning and ending of the years that might have shortened weeks). However using my code I keep getting a "Subtotal method of Range class failed" Basically I believe it doesn't like how I'm trying to use the array.

Worst case scenario, if I can't figure out a nice way to do it I'll just have to use case statements to define all the subtotal options.

 

[Nalani]

See if this works:

Sub Subtotal_Create()
    Dim ARString()
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
    x = lastcol - 7
    ReDim ARString(x - 8)
    For i = 8 To x
        ARString(i - 8) = i
    Next i
 
    ' Cells.Select  '//no need to Select whole sheet > choose a cell within data i.e. A5
 
    Range("A5").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=ARString, Replace:=True, _
        PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[BRich83]

Thank you so much. That method is what I wanted to do, but couldn't think of how to get it done.

Sub Subtotal_Create()
Dim ARString()
ReDim ARString(lastcol - 8)
 
For i = 8 To lastcol
    ARString(i - 8) = i
Next i

Range(Cells(1, 1), Cells(lastrow, lastcol)).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=ARString, Replace:=True, _
    PageBreaks:=False, SummaryBelowData:=True
 
End Sub