Macro for column sum
 

Hi all

I need a macro to add a column its sum, right below the end of the column.
Column length (i.e. # of rows) is variable.

For instance, let's say data is in D1:D50,
I want the column total in cell D51.
If the data was in D1:D15, I would want the sum in D16.


Thanks for help

Macro for column sum
 

Sub SelectFirstToLastInColumn()
Dim TopCell, BottomCell, x
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(16384, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell.Select
Else Range(TopCell, BottomCell).Select
x = Selection.Address
Cells(BottomCell.Row + 1, ActiveCell.Column).Select
ActiveCell.Formula = "=sum(" & x & ")"
End Sub

Macro for column sum
 

I dont understand where you close the If..Then blocks.
It doesn't work.

thanks for help




"excelent" wrote in message
...
Sub SelectFirstToLastInColumn()
Dim TopCell, BottomCell, x
Set TopCell = Cells(1, ActiveCell.Column)
Set BottomCell = Cells(16384, ActiveCell.Column)
If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell.Select
Else Range(TopCell, BottomCell).Select
x = Selection.Address
Cells(BottomCell.Row + 1, ActiveCell.Column).Select
ActiveCell.Formula = "=sum(" & x & ")"
End Sub

Macro for column sum
 

hope this help:

Sub SelectFirstToLastInColumn() ' line 1
Dim Top, Bot, x ' line 2
Set Top = Cells(1, ActiveCell.Column) ' line 3
Set Bot = Cells(16384, ActiveCell.Column) ' line 4
If IsEmpty(Top) Then Set Top = Top.End(xlDown) ' line 5
If IsEmpty(Bot) Then Set Bot = Bot.End(xlUp) ' line 6
If Top.Row = 16384 And Bot.Row = 1 Then ActiveCell.Select Else
Range(Top, Bot).Select ' line 7
x = Selection.Address ' line 8
Cells(Bot.Row + 1, ActiveCell.Column).Select ' line 9
ActiveCell.Formula = "=sum(" & x & ")" ' line 10
End Sub ' line 11

Macro for column sum
 

remember select column then run macro