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.

Solution

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.

Comments

[Jonathan Hopta]

I added a description of the scripts I have created and tried, but they do not satisfy what I'm attempting to do. I can't figure out how to get the Macro to recognize a variable length range of data. I found other sites that describe how to do it, but I haven't been successful using those steps. I am trying to determine: 1. Is what I'm attempting to do even possible? 2. Am I approaching it correctly and this is a true bug in Excel? 3. Am I missing a step in the macros I've created?

[Jonathan Hopta]

That works perfectly! I realize that I don't have a full understanding of the code and will need to brush up on it. But this solves my problem for now, much thanks!

[Jonathan Hopta]

I did attempt to create a macro that used the AutoSum function, but it didn't work. AutoSum is a nice feature, but it still would have required a lot of manual interaction. I'm attempting to automate the formatting I have to apply for a report I need to generate each week. Currently it takes me over an hour to do everything, so I'm attempting to automate as many steps as possible.

[pgr]

The Areas collection is the most interesting part. Excel does most of the work for you, recognizing contiguous areas of numbers. In fact, you could use it for more than one column. I'll edit my answer to include that.