Autosum variable range in macro
 

Hi Eric,

Here is one example of Visual Basic for Applications code to create a SUM
formula, based on the active cell, which sums contiguous data above it:

Sub testsum()
myrange = Range(ActiveCell.Offset(-1, 0).End(xlUp), ActiveCell.Offset(-1,
0)).Address(0, 0)
ActiveCell.Formula = "=sum(" & myrange & ")"
End Sub

Note that myrange is generated dynamically each time the macro is run. The
macro determines the range of contiguous data immediately above the active
cell, and creates the SUM formula based on that range. This method assumes
that all cells in the range to be summed contain data (no empty cells).

Hope this helps!
--
Tom Moore [MSFT]

This posting is provided "AS IS" with no warranties, and confers no rights.


"Eric Dreshfield" wrote in message
...
I would like to be able to add programming to a macro that
mimics the AutoSum key....for example, if cells A1 through
A10 have values and the active cell is A11, I want the
macro to create the =Sum(a1:a10) formula, but I do not
want the cell references hard coded, as the number of rows
will vary each time this process is run. Any suggestions ?

Thanks !

Autosum variable range in macro
 

Tom,

Thanks ! This worked just as I was hoping it would.

-----Original Message-----
Hi Eric,

Here is one example of Visual Basic for Applications code
to create a SUM
formula, based on the active cell, which sums contiguous
data above it:

Sub testsum()
myrange = Range(ActiveCell.Offset(-1, 0).End(xlUp),
ActiveCell.Offset(-1,
0)).Address(0, 0)
ActiveCell.Formula = "=sum(" & myrange & ")"
End Sub

Note that myrange is generated dynamically each time the
macro is run. The
macro determines the range of contiguous data immediately
above the active
cell, and creates the SUM formula based on that range.
This method assumes
that all cells in the range to be summed contain data (no
empty cells).

Hope this helps!
--
Tom Moore [MSFT]

This posting is provided "AS IS" with no warranties, and
confers no rights.


"Eric Dreshfield" wrote in message
...
I would like to be able to add programming to a macro
that
mimics the AutoSum key....for example, if cells A1
through
A10 have values and the active cell is A11, I want the
macro to create the =Sum(a1:a10) formula, but I do not
want the cell references hard coded, as the number of
rows
will vary each time this process is run. Any
suggestions ?

Thanks !


.