| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change the font size of excel row number and column heading | Excel Discussion (Misc queries) | |||
| change size of a cell | New Users to Excel | |||
| HOW DO I CHANGE THE FONT SIZE FOR A RANGE NAME? | Excel Discussion (Misc queries) | |||
| Change fmt of a cell if number matches any in a range of cells? | Excel Worksheet Functions | |||
| How do you change the size of a range of cells in a column/row | Charts and Charting in Excel | |||
Linear Mode
