Sum when rows of numbers varies

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,

Martin

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

[Tom Urtis]

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
CommandBars.FindControl(iD:=226).Execute
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