|P.S. I run into that problem all the time with client worksheets when I add new rows below the last value. |
Instead of editing the SUM formula itself, I select the cell when the SUM formula is and click a button on my toolbar which runs the macro below.
When the InputBox is presented, I simply select the entire range that I want to SUM and click OK.
I don't have to touch the keyboard or deal with editing the formula. It's 3 clicks and a mouse drag.
I also use the macro to set up new spreadsheets for new clients since it not only inserts the formula but formats the cell as well.
'Sum Range and Format Cells
On Error GoTo done
Set MyRange = Application.InputBox("Select Cells To Sum", Type:=8)
.Formula = "=SUM(" & MyRange.Address & ")"
.Font.Size = 8
.Font.Bold = False
.NumberFormat = "#,##0"
.HorizontalAlignment = xlRight
It fits in with my practice of "If I'm going to repeat the same task over and over again, it's proably more efficient to write a macro." The payback period for the time spent on the macro is usually pretty short for tasks that get repeated hundreds of times.