Sum up a dynamic range

[Guest]

Dear all,

I want to create a function (maybe just a common IW question in fact) to sum
up a column, which have a dynamic number of rows!

My user will retrieve data from DB and display in a column, but the number
of row of this column is subjected to change, so I can't hardcode
"=SUM(A1:A10)" at the end of the column. So I'd like to know how I can sum
and display the total right after the last cell of that dynamic column?

Thanks,

 

[papou]

Hello
Use a dynamic name for your range using this formula in the "refers To"
field:
=OFFSET(A1;;;COUNTA(A:A))
Then use this new name in your formula (which obviously must NOT be placed
in column A):
=Sum(YourName)

HTH
Cordially
Pascal

 

[Guest]

lastrow = Columns("A:A").Find("*", [A1], , , xlByRows, xlPrevious).Row
Range("A" & lastrow+1).formula="=SUM(A1:A" & lastrow & ")"


Regards,
Stefi

„Peter†ezt írta:

 

[Bob Phillips]

How about putting the formula

=SUM(A2:A65535)

in the first row, makes it easier to find as well.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)