Sum when rows of numbers varies

G

Guest

Guest
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top