Using Subtotal: How to update TotalList:=

[Guest]

Hi,
I am using Subtotal OK, but here is code for that:
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26,
27, 28, 29, 30, 31, 32, 33, 34, _
35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53,
54, 55, 56, 57, 58, 59, 60, _
61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73), Replace:=True,
PageBreaks:= _
False, SummaryBelowData:=False

As it is easy to see running numbers are the problem! It is increacing by
weeks. How could I made a code that would check the amount of columns used
and automatically adding this array?

Something similar to this (arrlist):
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=arrlist,
Replace:=True, PageBreaks:=False, SummaryBelowData:=False

Thanks in advance
BR
MakeLei

 

[Dave Peterson]

You start with 4 and go all the way to the right? No gaps, right?

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myArr() As Long
Dim iCol As Long
Dim StartCol As Long

StartCol = 4

'I used A1:???
Set myRng = Worksheets("sheet1").Range("a1").CurrentRegion

If myRng.Columns.Count < StartCol Then
MsgBox "Not enough columns to subtotal"
Exit Sub
End If

ReDim myArr(StartCol To myRng.Columns.Count)
For iCol = StartCol To myRng.Columns.Count
myArr(iCol) = iCol
Next iCol

myRng.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=myArr, Replace:=True, _
PageBreaks:=False, SummaryBelowData:=False

End Sub

 

[Guest]

Thanks Dave,
This was just what I was looking for.

Have a great Summer.
BR
MakeLei