Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Subtotal: How to update TotalList:=
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Subtotal: How to update TotalList:=
Thanks Dave,
This was just what I was looking for. Have a great Summer. BR MakeLei "Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
subtotal automatically update | Excel Programming | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Sort, Subtotal, Label Subtotal, Insert row | Excel Programming |