Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotalling - # of columns vary
I am trying to setup a template file and need to
programmatically insert subtotals, but different users may have more columns than others. The first 2 columns will be the same for all users and the data will always be a list with column names. The code that the recorder makes for subtotalling is this: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 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), Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True Any help would be appreciated. MC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotalling - # of columns vary
Dim varr()
Dim rng as Range Dim i as long set rng = Range(Range("A1"),Range("IV1").End(xltoLeft)) Redim varr(1 to rng.columns.count) i = 0 for each cell in rng if application.IsNumber(cell.offset(1,0)) then i = i + 1 varr(i) = cell.column end if Next Redim Preserve varr(1 to i) Selection.Subtotal GroupBy:=1, Function:=xlSum, _ TotalList:=varr, Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True Or if every column beyond 2 will be summed: Dim varr() Dim rng as Range, i as long set rng = Range("IV1").End(xltoLeft) Redim varr(1 to rng.column-2) for i = 1 to rng.column-2 varr(i) = i +2 Next Selection.Subtotal GroupBy:=1, Function:=xlSum, _ TotalList:=varr, Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True Code is untested and may contain typos. Regards, Tom Ogilvy "MDC" wrote in message ... I am trying to setup a template file and need to programmatically insert subtotals, but different users may have more columns than others. The first 2 columns will be the same for all users and the data will always be a list with column names. The code that the recorder makes for subtotalling is this: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 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), Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True Any help would be appreciated. MC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotalling | Excel Worksheet Functions | |||
Subtotalling | New Users to Excel | |||
Sum of columns that vary in position | Excel Worksheet Functions | |||
How can i vary the width of columns in a histogram? | Excel Worksheet Functions | |||
subtotalling where # of columns vary | Excel Programming |