predefined range being modified during tool run and giving error

[01's]

Hi,

I got a problem related to ranges. In a worksheet I select a range AB9 to AB50 and then name it as range1. When I delete or insert rows in the sheet I find the definition of range changes eg. from AB9 to AB 20 if I delete 30 rows. Same happens if i insert rows, the definition of range changes.

This happens during VBA script run where code for insertion/deletion are embedded.
How can I avoid change of range definition.

 

[njimack]

Define it as:
=OFFSET(Sheet1!$AB$1,8,0,42)

 

[01's]

Hi,

I define this range in an excel sheet. Not in my macro.
So when I use BELOW formula in name manager the range is not seen in drop down menu list in left corner.
Confused Below is what I defined.
=OFFSET(INDIRECT("BS!AB9"),0,0,1000,1)

Also I use the range name in some formulas and they show "#N/A" after I change the definition to above.

where am I going wrong?

 

[njimack]

It makes no difference how you define it - VBA or manually.
Dynamic names and those consisting of functions won't show up in the drop-down menu - this is normal.

Not sure why you're using INDIRECT, and you should be referring to row 1 and then offsetting by 8 rows. If you refer to row 9, you'll still experience the same problem.