How to update range macro when cells in Excel are inserted/deleted.

Question

I would like to ask if there is a way to update the range specified in a macro when I insert/delete cells..

For example, I have specified the range in a subroutine in my macro to be

Worksheets("Query Screen").Range("M22:M25") = "0"

Now, if I insert a new row between M22 and M25, what can I do to update the range in my macro automatically to be

Worksheets("Query Screen").Range("M22:M26") = "0"

Thus, is it possible to update the macro based on changes in the excel spreadsheet?

Answer 1

As far as I know this isn't possible.

But what you can do:

Give the range you are working on a name: e.g MyRange

After that you can use

Range(ActiveWorkbook.Names("MyRange").RefersTo) = 0

That will automatically be updated after inserting rows or columns