Range to change size according to number in another cell

[JohnUK]

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 that’s 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 IF’s, but I just need a
short piece of code that can do it more efficiently.
Help greatly appreciated
Regards
John

 

[Rick Rothstein]

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".

 

[Ryan H]

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.

 

[Rick Rothstein]

That should have read...

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

 

[JohnUK]

Ryan, You are a star, many thanks.

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.

 

[JohnUK]

Many thanks for help also Rick

That should have read...

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

--
Rick (MVP - Excel)




.