If I understand what you want to do I do something similar without
going into VBA.

=SUM(OFFSET($A$1,0,0,ROW()-2,1))

... Will sum  everything in column A from A1 to 2 rows  above the cell
the formula is in.
If you add rows in between it don't matter... the formula takes care
of that.