Updating Ranges or References in Code when Adding or Deleting Rows

[Arnold]

Greetings Programmers,

Would there happen be a way to update all ranges or references in
modules when one or more rows are added or deleted?

Reason I ask is that the 'heading' row on a sheet starts on row 12,
and users may wish to add or remove rows above this heading line.
Examples of code with set ranges are:

Set ActivityDate = ActiveSheet.Range("C11")
and
ActiveSheet.Cells(lastcolumn + 1).Offset(12, 0).Select

....so references such as C11 and (12,0) would be affected.

Just curious.
Thanks,
Arnold

 

[Guest]

One way to do that would be to create named ranges (insert=>Name=>define) and
refer to those in your code rather than absolute cell addresses. The formula
specifying the location of the named range will adjust like any other formula
in excel and the name will continue to point to the same cell, even though it
has moved.

Another way is to calculate the location from some base location or use the
find method to find a reference value and offset from there.