Modify a range name with vb

[jean]

Hi

I have a name define named "DropResource" and the range is A10:A20

If somebody add data to A21, A22 and A23, I would like the vb code
that will extend the range to A23 so it become A10:A23

Same thing if someone delete data in A19 and A20, the range become
A10:A18

I have try with a macro and put the active cell on A10 and go with
Shift End Down but it doesn't work because macro return a fix R and C
reference.

I would like code that use XL(down) but I don't succeed

Thanks for helping

 

[Bob Umlas, Excel MVP]

You don't need a macro - the name can define itself:
Define DropResource as
=OFFSET($A1$0,0,0,COUNTA($A$10:$A$500),1)
As data is added/deleted, the range expande contracts.
It won't show up in the Go To dialog, but you can go to it nonetheless, and
refer to it in formulas.

 

[Don Guillett]

small typo

=OFFSET($A1$0,0,0,COUNTA($A$10:$A$500),1)

=OFFSET($A$1,0,0,COUNTA($A$10:$A$500),1)
and IF?? you deleted a lot of rows then this may be best
=OFFSET($A$1,0,0,COUNTA($A:$A),1)