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).

Macro use case screen shot

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.

  • 1
    Welcome to SU, unfortunately we're not a script writing service, but we can help you with specifics in your script. What have you go so far? Where are you getting stuck exactly? – Ƭᴇcʜιᴇ007 Feb 13 '15 at 17:37
  • 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 Feb 13 '15 at 17:43

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.

  • 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 Feb 13 '15 at 18:05
  • 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. – pgr Feb 13 '15 at 18:11

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.

  • 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. – Jonathan Hopta Feb 13 '15 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.