Range to change size according to number in another cell

  • Thread starter Thread starter JohnUK
  • Start date Start date
J

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
 
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".
 
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.
 
Ryan, You are a star, many thanks.

Ryan H said:
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.
Click to expand...
 
Back
Top