That should have read...

"=Sheet1!R4C6:R" & Range("A2").Value & "C7"

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The value being assigned (the part to the right of the first equal sign)
is nothing more than a String value, so you can concatenate together
whatever you need to. Assuming A2 is the "other cell" (the one with the
4000 and/or 500 values you mention), then use this for the assignment....

"=Sheet1!R4C6:R" & A2 & "C7"

Just change the A2 to the address of your "other cell".

--
Rick (MVP - Excel)


"JohnUK" wrote in message
...
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