Try this line. I didn't know which cell you wanted to reference for your
range size so you'll have to a adjust it to your application.

ActiveWorkbook.Names("Range_B").RefersToR1C1 = "=Sheet1!R4C6:R" &
Range("A1").Value & "C7"

Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan


"JohnUK" wrote:

Hi, Can anyone help with this.
I need a range to change its size according to a number in anther range/Cell.
For example, this is the range thats spans down 4000 rows:

ActiveWorkbook.Names("Range_B").RefersToR1C1 = "=Sheet1!R4C6:R4000C7"

And if the other Range/Cell = 500, I would want the Rang_B to change to 500
rows

I can put something together consisting of lots of IFs, but I just need a
short piece of code that can do it more efficiently.
Help greatly appreciated
Regards
John