Add a column of numbers without cell or worksheet references

[joecollege]

I can't believe I can't find the answer to my question.
I have a column of numbers, which can have 2 rows or 150 rows.
I want to put the cursor at the bottom of the row and get the sum of the column (with formula) Exactly the same way as using the Sum Function in the ribbon.
I don't want to use worksheets or cell references because I want to use this macro in all my spreadsheets and macros.
I don't want to use the Sum(Alt +=) in the ribbon.

Example:

10
16
3
29 is the answer (the formula is that column's top row and last row, for example, A1:A3.

This macro will work in any column and not just column A


Now, this macro will work but it is too long and cumbersome.

Sub Sum2()

Dim fr As Long

With ActiveCell
Select Case .Row
Case 1
fr = 0
Case 2
fr = 1
Case Else
If IsEmpty(.Offset(-1).Value) Then
fr = 0
ElseIf IsEmpty(.Offset(-2).Value) Then
fr = .Row - 1
Else
fr = .Offset(-1).End(xlUp).Row
End If
End Select
If fr > 0 Then
.FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
Else
.Value = "Error"
End If
End With
End Sub

Your help and wisdom would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

Now, this macro will work but it is too long and cumbersome.

On the contrary, that macro looks pretty short to me!
What don't you like about it?

Note that you shouldn't pay too much attention to how long a macro is. That is not the most important thing.
What is more important is if it does what you want in a timely/efficient manner.