Variable range

=A1:OFFSET(A1,,,COUNTA(A:A))

--

HTH

RP

"Chuckles123" wrote in message
...

Dave and Patrick,
Thanks very much for your responses. However, my macro is still not
working.
I appreciate the comment about mixing relative and absolute addressing
in my formula; but it appears to be a non-fatal error. I say this
because I was able to get the formula = SUM(J3:J99) in the appropriate
cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was able
to get the numerical result to display.

Which, part of my original question, is why can I not send the {F2} key
from the macro to the WorkSheet (as opposed to opening the Object
Browser)?

Patrick, I like your approach because it does not require insertion of
the Row number of the cell adjacent above the sum formula in a cell
away from the range to be summed. But I get an "Application-Defined or
object-defined error" message when using your Target.FormulaR1C1 =
"=SUM(R3C:R[-1]C".

Dave, I am using the row 4 rows down from the sum formula to display
the row number of the cell adjacent above the sum formula (the Active
Cell). In your ActiveCell.Formula, the compiler is having difficulty
with the .Offset.
I would appreciate your continuing help.
Chuckles123

P.S.: A related issue is the ability to name a variable-length column
or row in a macro. Any thoughts?


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=265895