Simple macro to AutoSum

Posted by Glynn on January 30, 2001 10:53 AM

Im trying to write a simple macro to AutoSum. Example: Cell A1 is empty, A2=2, A3=4, A4=6, cells A5 thru A7 are empty, A8=2, A9=4, A10=6, A11=8. I go to cell A5 and perform the following actions:

 Click on Record New Macro
 Click the AutoSum button on the tool bar
 Hit the Enter key
 Click the Stop Recording button

The macro wrote the formula =SUM(A2:A4) that yields 10 in cell A5. Now I go to cell A12 and execute the macro and it writes the formula =SUM(A9:A11) and yields 18 in cell A12. The macro did not produce a formula that included cell A8. I want to write a macro that will sum all the cells up to the first empty cell above, no matter where the macro is executed.

Thanks to anyone that will help.

Glynn

Posted by faster on January 31, 2001 5:10 PM

Sub MySum()
'note "ALT" and "=" will guess the range to sum
'I thought I would give the code a shot though
'run macro from blank cell at bottom of numbers to be added
Dim SumCell
SumCell = ActiveCell.Address

Dim Anchor
Selection.End(xlUp).Select
Anchor = ActiveCell.Address

Selection.End(xlUp).Select
Dim Top
Top = ActiveCell.Address

Range(SumCell).Select

ActiveCell = "=SUM(" & Top & "," & Anchor & ")"

End Sub

Posted by Glynn on February 01, 2001 8:08 AM

Sub MySum() 'note "ALT" and "=" will guess the range to sum 'I thought I would give the code a shot though 'run macro from blank cell at bottom of numbers to be added Dim SumCell SumCell = ActiveCell.Address Dim Anchor Selection.End(xlUp).Select Anchor = ActiveCell.Address Selection.End(xlUp).Select Dim Top Top = ActiveCell.Address Range(SumCell).Select ActiveCell = "=SUM(" & Top & "," & Anchor & ")" End Sub

Posted by Glynn on February 01, 2001 8:10 AM

Sub MySum() 'note "ALT" and "=" will guess the range to sum 'I thought I would give the code a shot though 'run macro from blank cell at bottom of numbers to be added Dim SumCell SumCell = ActiveCell.Address Dim Anchor Selection.End(xlUp).Select Anchor = ActiveCell.Address Selection.End(xlUp).Select Dim Top Top = ActiveCell.Address Range(SumCell).Select ActiveCell = "=SUM(" & Top & "," & Anchor & ")" End Sub

Thank You
The macro works perfectly