subtotalling - # of columns vary

[MDC]

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

 

[Tom Ogilvy]

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