I have a form control button I am trying to use to delete a range of cells and refill, but I do not want the formula references to shift if I add rows/columns. I am aware of naming the range but I cannot think of an appropriate way to apply that fix in this scenario.
Ideally I would like to do something as simple as "delete I20:J20" , then "undo" but obviously you can't undo within a macro as far as I know. What I have resorted to is deleting this range, then retyping the formulas (using record macro because I am way too amateur for writing VBA). However since I have to retype the formula in the first cell of the row before spreading it right, the named range solution is of little use as the individual cells that i re-type the formula in do not utilize the named range, only the initial deletion does.
How can I implement this, I find it surprising there is no way to fix cell references within a macro aside from naming the range.
Ideally I would like to do something as simple as "delete I20:J20" , then "undo" but obviously you can't undo within a macro as far as I know. What I have resorted to is deleting this range, then retyping the formulas (using record macro because I am way too amateur for writing VBA). However since I have to retype the formula in the first cell of the row before spreading it right, the named range solution is of little use as the individual cells that i re-type the formula in do not utilize the named range, only the initial deletion does.
How can I implement this, I find it surprising there is no way to fix cell references within a macro aside from naming the range.
