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 |
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 |
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 |
All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com