Add a column of numbers without cell or worksheet references

joecollege

New Member
Joined
Sep 23, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,038
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,564
Messages
5,741,856
Members
423,691
Latest member
Fahad987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top