Using Subtotal: How to update TotalList:=
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
Makelei wrote:
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
|