Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Sum when rows of numbers varies

  1. #1


    Dear Mr. Excel,

    Say I have a row of numbers, e.g. from A1:A10 and I have a macro as follows:

    Cells(1, 1).End(xlDown).Offset(1).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"

    If the row of numbers changes, say from A1:A7, then the macro doesn't work good anymore!
    How can I make the macro work when the row of numbers varies!

    Best regards,


    PS The "End(xlDown).Offset(1)" part must stay in the macrocode!

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    San Francisco, California USA
    Post Thanks / Like
    1 Post(s)
    1 Thread(s)


    There are a few ways to do this; here's just one. Since you want that line of code to remain in the macro, try this:

    Sub Summ()
    Cells(1, 1).End(xlDown).Offset(1).Select
    Application.SendKeys "~"
    End Sub

    The reason your original macro didn't work is that it was referencing a hard-coded relative range.

    You should be aware though, that you limit your margin for error because of that code you want to keep, because you must remember to keep all cells in column A populated, from and including A1, without any broken (empty) cells. As an alternative, if you went to the first truly unused cell in column A by searching from the bottom up, instead of from the top down as in your case, then you you wouldn't have to worry about an incorrect sum showing up in the first innocently empty cell, if further below are cells with values that should also be included in the sum. Just FYI.

    Tom Urtis

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts