Need an Excel Macro to sum a variable length range

Question

I am trying to create a Macro that is able to recognize how many rows have values above it and only sum that range. If you look at the attached image, I need to be able to click in cells B4, B10, and B21 and execute the macro and have it only sum the values in that range.

So for cell B4, I need the macro to know that it needs to sum cells B1 to B3.

For cell B10, I need the macro to automatically know that it needs to sum the range of 4 cells (from B6 to B9).

For cell B21, I need the macro to automatically identify that the range to sum is now 9 rows (B12 to B20).

The Macros I have used will either only Sum a set range of cells (e.g. will only sum the 5 cells immediately above the cell where the macro is executed from (see code snip below):

Sub Macro22()
'
' Macro22 Macro
' With Relative References turned on.
'
' Keyboard Shortcut: Ctrl+o
'
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
End Sub

My second attempt will cause the macro to sum all cells above it.

Sub test()
ActiveCell.Value = "=SUM(" & Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row - 1, ActiveCell.Column)).Address(False, False) & ")"
End Sub

Is this even possible? From what I've seen it should be possible, but it's super tricky to get the macro to do it.

Answer 1

And if you really want a macro, use this (taken mostly from here, with some improvements):

Option Explicit

Sub AutoSum()
    Const SourceRange = "C:F"
    Dim NumRange As Range, formulaCell As Range
    Dim SumAddr As String
    Dim c As Long

    For Each NumRange In Columns(SourceRange).SpecialCells(xlConstants, xlNumbers).Areas
        SumAddr = NumRange.Address(False, False)
        Set formulaCell = NumRange.Offset(NumRange.Count, 0).Resize(1, 1)
        formulaCell.Formula = "=SUM(" & SumAddr & ")"

        'change formatting to your liking:
        formulaCell.Font.Bold = True
        formulaCell.Font.Color = RGB(255, 0, 0)

        c = NumRange.Count
    Next NumRange

End Sub

This works on numbers in columns C to F, change that to any other range you want in the beginning of the code (constant SourceRange).

Just make sure your areas of numbers don't touch each other.

Answer 2

Instead of a macro, it seems to me that what you ask is already part of Excel as a feature, called AutoSum.

Just place the cursor under a contiguous set of numbers (a region, in Excel-speak), and press the AutoSum button. Excel will come up with the formula for you.

The AutoSum button is on the ribbon, on the "Home" tab, far to the right, and has an icon like a sigma (∑).

Here is a tutorial.