View Full Version : [SOLVED:] Setting .SubTotal TotalList via VBA
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
04-04-2005, 09:32 AM
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,
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.