Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Sort A-Z a variable range of cells | Excel Discussion (Misc queries) | |||
Macro - Using a variable number of columns in a Range | Excel Discussion (Misc queries) | |||
Range used in a macro needs to be variable | Excel Worksheet Functions | |||
variable range in a macro | Excel Discussion (Misc queries) | |||
Is it possible to set a variable range in a macro? | About this forum |