Named range changes after columns inserted, how to fix?

[kruuth]

Is there a way to "lock" a range? I have a range of values defined as A:G on a sheet. However I have some VBA code that inserts a few columns into that sheet. After that happens the range gets altered to something like H:K. Is it possible to lock this? If not, what is the VBA code to change the range of values for a pre-defined range?

 

[MickG]

You could change it to its original range within the column insertion code.

Range("C1:E10").Name = "MyRng"

 

[kruuth]

How would I do this later? It looks like I might have to fix a multitude of ranges on different sheets.

 

[MickG]

You could place it in the worksheet "Activate" event for each sheet, but if
if the sheet was open and you added the columns , the range would change and not be reinstated.
I don't know your exact circumstance but, I would have thought that if you did all the renaming as you added the columns it would be best.
You would need a list a the bottom of you code like this:-

Sheets("Sheet10").Range("C1:E10").Name = "MyRng"
Sheets("Sheet15").Range("A1:C5").Name = "ShtRng"

 

[kruuth]

Awesome. That worked.