excel question

How do you set up a formula to contain a dynamic range for example the sum()

=sum(A1:A72) works if you know the range starts at A! and end at A72

what if the lower limit is unknown???

key strokes shown below
=sum(A1:<endkey><Down arrow key>)

yields this
=sum(A1:aX) where X is the current activecell row in column A

how do I build this in a Excel VB macro???

I currently have a macro that when ever a button gets pushed a row is added to the bottom of a growing list I them want to sum up different totals but I want to use the dynamic range for the sum function

when ever I do a record macro to help me determine the proper syntax I only get the final result not the actions to get to the final result

I get something like
ActiveCell.Value= "=sum(A2:A17)" which is great for just this one instance then next time the macro would run the value would have to change from a17 to A18 and so on and so on

Thanks for your assistance
Jeff

excel has a few different ways to expand cell / range objects to fill areas - equivalent to pressing ctrl-arrow or the home end keys etc.

i think what you need is the end property that each cell / range has:i haven't tested this - it's off the top of my head, more or less

You could also try using a named range and putting the name in the formula

e.g. set a named range of DATATOSUM = A1:A1000

the forumla =SUM(DATATOSUM)

Otherwise, to do it in VBA macros, you'd need to first find the last row (use xlup function) and store it in a variable

Then create your formula based on the variable.

..... "=sum(a2:" & range("a2").end(xldown).address(false,false) & ")"


Your code above is very usful, i need a code that does that but with one more twist. I have labled an int and i need it to automatically change clumns with a click of a button, so it will sum up say colum A, then click again and it will sum up column B. This is what i tried, but it does not work, can anybody give me some advice


the code is where k is the int, and k is incremented


"=sum(" & K & "7:" & Range(K & "7").End(xlDown).Address(False, False) & ")"

Thanx

i would suggest you use the cells(row,column) notation rather than the range(address) notation as follows:
you may need be careful with activesheet etc. if you plan to use this over multiple sheets, btw, but it's easily adjusted to cope

bracket error corrected as below

Thnx for the quick reply EPL, appreciated greatly!

I have a question still, (i still have an error) the

"=sum(" & Range(Cells(7, K), Cells(2, K)).End(xlDown).Address(False, False) & ")"

The second Cells(2,k)...... What wxactly is that saying, do you still not have to specify the end of the column? Cells(2,k), so how does that reach the end of the clumn when it is not hard coded?


Thnx

yes - sorry the second cells(..).end sepcifies the end column (the range to be summed is the range(top,bottom) from the top cell(7,k) to the bottom cell(7,k).end(...) .. if that's any clearer!

so the problem is with my breacket placement - there should only be one bracket after the second cell(7,k), with the .end(...) as a subproperty of the cell, not the range - the "spare" bracket then goes after the end(...) as follows:apologies and hope that helps ... i'll edit the above error, if you don't mind

Thank you, again your help is cherished.

That has solved my problem, hopfully i can repay the favor some time.


Take care EPL