Range to change size according to number in another cell
 

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

Range to change size according to number in another cell
 

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

Range to change size according to number in another 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.
--
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

Range to change size according to number in another cell
 

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

Range to change size according to number in another cell
 

Ryan, You are a star, many thanks.

"Ryan H" wrote:

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

Range to change size according to number in another cell
 

Many thanks for help also Rick

"Rick Rothstein" wrote:

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



.