How to use macro to set "Sum" function

[New user]

Do anyone know if i want to set a macro which i want to comprise 2 elements:

1: total underline
2: sum ("Sum" function in Excel which automatically sum up all the cells
with figures)

Every time i set, the no. of cells to be added are fixed. for example, Col A
has 3 cells to sum up; when i apply it to Col B which has 5 cells, it also
only add up the last 3 cells, not 5.

Can anyone advise me on this matter?

Thank you so much.

 

[Roger Govier]

Hi

Try the following macro.
It assumes you have selected the range of cells you wish to Sum, then run
the macro.

Sub addup()

Dim i As Long, lr As Long, j As Long
Dim rng As String, rng1 As String, rng2 As String
With Selection
i = .Row
j = .Column
lr = .Rows.Count + 1
End With
rng1 = Cells(i, j).Address
rng2 = Cells(lr - 1, j).Address
Cells(lr, j) = "=SUM(" & rng1 & ":" & rng2 & ")"
With Cells(lr, j).Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

End Sub