Setting .SubTotal TotalList via VBA

[MWE]

When using the SubTotal Method,

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

one of the arguements is TotalList. Per VBA Help:

TotalList Required Variant. An array of 1-based field offsets, indicating the fields to which the subtotals are added.

I have tried to define or set TotalList several different ways, but other than something similar to code snipet above, nothing has worked. I would have thought that a simple variant array would work, but it does not.

How can I programatically set TotalList?

Thanks

[mvidas]

MWE,

It looks like you want a macro that will always perform the subtotal on the selection, using the first selected column as the "group by", and each additional column as the TotalList?
If you wanted 2, 3, 4, 5 to be the TotalList, and "arr" was your variant array, you would need to assign it like:

arr(0) = 2
 arr(1) = 3
 arr(2) = 4
 arr(3) = 5

But of course, that is knowing what fields you need. You can do it programatically like:

Sub subt()
 Dim TotalListArr(), i As Long, j As Long
 ReDim TotalListArr(Selection.Columns.Count - 2)
 For i = 2 To Selection.Columns.Count
  TotalListArr(j) = i
  j = j + 1
 Next i
 Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotalListArr, _
  Replace:=True, PageBreaks:=False, SummaryBelowData:=xlSummaryBelow
End Sub

Any questions, let me know!
Matt

[MWE]

MWE,

It looks like you want a macro that will always perform the subtotal on the selection, using the first selected column as the "group by", and each additional column as the TotalList?
If you wanted 2, 3, 4, 5 to be the TotalList, and "arr" was your variant array, you would need to assign it like:

arr(0) = 2
 arr(1) = 3
 arr(2) = 4
 arr(3) = 5

But of course, that is knowing what fields you need. You can do it programatically like:

Sub subt()
 Dim TotalListArr(), i As Long, j As Long
 ReDim TotalListArr(Selection.Columns.Count - 2)
 For i = 2 To Selection.Columns.Count
  TotalListArr(j) = i
  j = j + 1
 Next i
 Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotalListArr, _
  Replace:=True, PageBreaks:=False, SummaryBelowData:=xlSummaryBelow
End Sub

Any questions, let me know!
Matt

The only difference between what you suggest and what I tried was that I dimmed and typed the TotalList array, i.e.,

Dim varTotalList(5) as variant

My loop to stuff values in the array is virtually identical. Execution halted with an error statement indicating that TotalList:=varTotalList was unacceptable. I will try the non-typed approach.