excel - Summing Dynamically Varying Columns using VBA -

i need calculate sum of values in each column , store in array. please note number of columns not fixed. varies dynamically. totally stuck. part of huge function writing.

in below code, "column h" summing variable one. mean number of columns calculate sum based on value of val(i). eg: if val(0) = 10, need calculate sum of numbers starting column h till column q, , storing sum of each column in array, i.e.sum(0) = sum of column h; sum(1) = sum of column i; , on.

the point stuck increment column, i.e. after summing "column h" in next iteration should sum elements in "column i"----> (sum(i + k) = application.worksheetfunction.sum(range("h2"))

i have tried below:

dim i, j, k, l, maxval integer dim objnewsheet worksheet dim sum(0 1000) double  k = 0 = 0 (maxval-1)     set objnewsheet = activeworkbook.sheets("sheet" & (i+1))     objnewsheet.select     j = 0 val(i)         sum(i + k) = application.worksheetfunction.sum(range("h2"))         k = k + j     next j next 

as tim mentions, can use resize method modify size of range.

dim rng range: set rng = range("h2") sum(i + k) = application.worksheetfunction.sum(rng.resize(1, j)) 

the above should create new range j columns wide.

i add range variable because easier work (imo) constants range("h2"). can use msgbox rng.resize(1,j).address view new range's address.

here few other points may want consider:

• you have not declared variables correctly, unless intend i, j, k, l variant. do:

  dim i#, j#, k#, l#, maxvalue# (shorthand notation) or

  dim integer, j integer, k integer, l integer, maxvalue integer

  otherwise, unless specify type each variable, variant.

• for readability, may want avoid naming variables after reserved or semi-reserved keywords or existing functions sum. rename variable mysum or sumarray or else indicate future users variable, not function.

• i mention in comment above dimensioning array variable upper-bound of 1000 kind of clunky, , fail if ever encounter need larger array. may not case, it's still clunky way of dimensioning array. suggest dim mysum() double , redim variable later in code, perhaps using redim preserve if needs dynamic @ run-time.

update

here simple example, code snippet/incomplete, cannot use it, example should illustrate how works.

option base 1 sub test()     dim integer     dim rng range     dim mysum() double      '## initial range ##'     set rng = range("h2")      '## iterative loop ##'     = 1 4          '## resize range within iteration ##'         set rng = rng.resize(1, i)  '<-- change 1 in line number of rows in column needs summed'         '## ensure array dimensioned ##'         redim preserve mysum(i)         '## store calculation in array ##'         mysum(i) = application.worksheetfunction.sum(rng)      next  end sub