How to make a range automatically update when rows/cells are added

[poiu]

Hello,

If I have the following range:

Set output_data_range_part_1 = Sheets("Dates database") _ 
.Range("D1:G10")

please can you tell me how to make this range automatically update if, say, a column is added between columns E and F? e.g. in this case I would want the range to change to:

Set output_data_range_part_1 = Sheets("Dates database") _ 
.Range("D1:[B]H[/B]10")

Thanks,

Poiu

 

[Gary McMaster]

Hi Poiu,

If you name the range and refer to it by that name, changes in rows & columns will be tracked automatically.

There is also the "Resize" method if you intend to force the change in code.

Gary

 

[poiu]

If you name the range and refer to it by that name, changes in rows & columns will be tracked automatically.

Thanks Gary, are you referring to setting up a name range outside of VBA and then calling it within the VBA code?

 

[Gary McMaster]

Either way. You can create and name a range in VBA or from the Excel GUI.

Gary

 

[poiu]

Hi Gary,

Thanks but I'm a bit confused, please could you tell me if this is the correct way to declare a name range in VBA for my example above?

Sheets("Dates database").Range("D1:G10").Name = "the_name_of_my_name_range"

Thanks a lot,

Poiu

 

[Gary McMaster]

That works for me (XL 2000)

Now if you insert a new row or column that intersects that named range (in VBA or from the Excel GUI) the range will expand and include the new row and/or column.

Gary

 

[poiu]

Thanks Gary, thanks wonderful!