Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range with multiple groups of cells
Hello,
I am attempting to use named ranges consisting of quite a few cells which are not joined in a group. Ive created them by selecting the first cell of the range, then holding down the Ctrl button and selecting the rest, before giving the range a name. Im running into problems when I need to add or remove a cell in one of the ranges. Say for example a range consisting of thirty separate cells; when looking at the list in the €śDefine Name€ť box, and trying to see the cells already in the range along the €śRefers to:€ť line, I can only see about the first five or six. If I click in the line and try to view further along the list, it appears to add extra random cells to it, and if I type in a new cell at the front, it doesnt seem to accept it. Im using Excel 2003, by the way. The way Ive been getting round this is by deleting the range and recreating it with the added cells. This is tedious and seems morally wrong. Ive been experimenting with recording Macros to see if I can maintain a range that way, but it doesnt work very well. Here are my examples: 1. Sub Macro1() Range("A1:A4").Select ActiveWorkbook.Names.Add Name:="Range_1", RefersToR1C1:="=Sheet1!R1C1:R4C1" End Sub 1. This one is ok, because its just a group together. I can increase the size by changing the text above from "=Sheet1!R1C1:R4C1" to "=Sheet1!R1C1:R5C1". 2. Sub Macro2() Range("B1,B3,B5,B7").Select Range("B7").Activate ActiveWorkbook.Names.Add Name:="Range_2 ", RefersToR1C1:= _ "=Sheet1!R1C2,Sheet1!R3C2,Sheet1!R5C2,Sheet1!R 7C2" End Sub 2. This one's not so good. If I add Sheet1!R9C2 to it, nothing happens. If I insert some rows between rows three and five the cells in the range remain correct, but the cell references in the VB text dont change. I know I can change these manually, but Im trying to keep things as simple and efficient as possible. Also, a list of even thirty of these in the VB text makes one single very long ugly line, which I dont seem to be able to reduce by putting it on multiple lines. Each one of the cells is individually named and most are quite long names by neccessity. This doesn't help with the reduction of text either. Does anybody know a more efficient way of doing this? My knowledge of VB is very limited, consisting of a few basic instructions, so I would really appreciate some help. Thank you Kind regards Clinton |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range with multiple groups of cells
When you click in the Refers to box press function key F2. That'll put you
in Edit Mode. Look at the bottom left corner of the Staus bar and it will say Edit. If it says Enter or Point then using the directional arrow keys will do what you're describing. When this happens to me I usually cuss real loud! You can make the Refers to box a little bit wider by clicking the icon on the far right side of the box. -- Biff Microsoft Excel MVP "Clinton W" wrote in message ... Hello, I am attempting to use named ranges consisting of quite a few cells which are not joined in a group. I've created them by selecting the first cell of the range, then holding down the Ctrl button and selecting the rest, before giving the range a name. I'm running into problems when I need to add or remove a cell in one of the ranges. Say for example a range consisting of thirty separate cells; when looking at the list in the "Define Name" box, and trying to see the cells already in the range along the "Refers to:" line, I can only see about the first five or six. If I click in the line and try to view further along the list, it appears to add extra random cells to it, and if I type in a new cell at the front, it doesn't seem to accept it. I'm using Excel 2003, by the way. The way I've been getting round this is by deleting the range and recreating it with the added cells. This is tedious and seems morally wrong. I've been experimenting with recording Macros to see if I can maintain a range that way, but it doesn't work very well. Here are my examples: 1. Sub Macro1() Range("A1:A4").Select ActiveWorkbook.Names.Add Name:="Range_1", RefersToR1C1:="=Sheet1!R1C1:R4C1" End Sub 1. This one is ok, because it's just a group together. I can increase the size by changing the text above from "=Sheet1!R1C1:R4C1" to "=Sheet1!R1C1:R5C1". 2. Sub Macro2() Range("B1,B3,B5,B7").Select Range("B7").Activate ActiveWorkbook.Names.Add Name:="Range_2 ", RefersToR1C1:= _ "=Sheet1!R1C2,Sheet1!R3C2,Sheet1!R5C2,Sheet1!R 7C2" End Sub 2. This one's not so good. If I add Sheet1!R9C2 to it, nothing happens. If I insert some rows between rows three and five the cells in the range remain correct, but the cell references in the VB text don't change. I know I can change these manually, but I'm trying to keep things as simple and efficient as possible. Also, a list of even thirty of these in the VB text makes one single very long ugly line, which I don't seem to be able to reduce by putting it on multiple lines. Each one of the cells is individually named and most are quite long names by neccessity. This doesn't help with the reduction of text either. Does anybody know a more efficient way of doing this? My knowledge of VB is very limited, consisting of a few basic instructions, so I would really appreciate some help. Thank you Kind regards Clinton |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range with multiple groups of cells
i usually cuss, too. i wish they would have put those status messages in the dialog
instead of the status bar. i think i would notice them more readily. -- Gary Keramidas Excel 2003 "T. Valko" wrote in message ... When you click in the Refers to box press function key F2. That'll put you in Edit Mode. Look at the bottom left corner of the Staus bar and it will say Edit. If it says Enter or Point then using the directional arrow keys will do what you're describing. When this happens to me I usually cuss real loud! You can make the Refers to box a little bit wider by clicking the icon on the far right side of the box. -- Biff Microsoft Excel MVP "Clinton W" wrote in message ... Hello, I am attempting to use named ranges consisting of quite a few cells which are not joined in a group. I've created them by selecting the first cell of the range, then holding down the Ctrl button and selecting the rest, before giving the range a name. I'm running into problems when I need to add or remove a cell in one of the ranges. Say for example a range consisting of thirty separate cells; when looking at the list in the "Define Name" box, and trying to see the cells already in the range along the "Refers to:" line, I can only see about the first five or six. If I click in the line and try to view further along the list, it appears to add extra random cells to it, and if I type in a new cell at the front, it doesn't seem to accept it. I'm using Excel 2003, by the way. The way I've been getting round this is by deleting the range and recreating it with the added cells. This is tedious and seems morally wrong. I've been experimenting with recording Macros to see if I can maintain a range that way, but it doesn't work very well. Here are my examples: 1. Sub Macro1() Range("A1:A4").Select ActiveWorkbook.Names.Add Name:="Range_1", RefersToR1C1:="=Sheet1!R1C1:R4C1" End Sub 1. This one is ok, because it's just a group together. I can increase the size by changing the text above from "=Sheet1!R1C1:R4C1" to "=Sheet1!R1C1:R5C1". 2. Sub Macro2() Range("B1,B3,B5,B7").Select Range("B7").Activate ActiveWorkbook.Names.Add Name:="Range_2 ", RefersToR1C1:= _ "=Sheet1!R1C2,Sheet1!R3C2,Sheet1!R5C2,Sheet1!R 7C2" End Sub 2. This one's not so good. If I add Sheet1!R9C2 to it, nothing happens. If I insert some rows between rows three and five the cells in the range remain correct, but the cell references in the VB text don't change. I know I can change these manually, but I'm trying to keep things as simple and efficient as possible. Also, a list of even thirty of these in the VB text makes one single very long ugly line, which I don't seem to be able to reduce by putting it on multiple lines. Each one of the cells is individually named and most are quite long names by neccessity. This doesn't help with the reduction of text either. Does anybody know a more efficient way of doing this? My knowledge of VB is very limited, consisting of a few basic instructions, so I would really appreciate some help. Thank you Kind regards Clinton |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range with multiple groups of cells
I build a range (without the .select's) and then use that range.
Dim myRng as range with activesheet set myrng = .Range("B1,B3,B5,B7") myRng.name = "MyRangeNameHere" 'or to make it local to that sheet myrng.name = "'" & .name & "'!MyRangeNameHere" end with If I'm doing it manually, I can select the range (click and ctrl-click). Then use alt-F11 (to get to the VBE) hit ctrl-g (to see the immediate window and type this: Selection.name = "myRangeNameHere" (and hit error) or Selection.name = "'sheet name here'!myRangeNameHere" Clinton W wrote: Hello, I am attempting to use named ranges consisting of quite a few cells which are not joined in a group. Ive created them by selecting the first cell of the range, then holding down the Ctrl button and selecting the rest, before giving the range a name. Im running into problems when I need to add or remove a cell in one of the ranges. Say for example a range consisting of thirty separate cells; when looking at the list in the €śDefine Name€ť box, and trying to see the cells already in the range along the €śRefers to:€ť line, I can only see about the first five or six. If I click in the line and try to view further along the list, it appears to add extra random cells to it, and if I type in a new cell at the front, it doesnt seem to accept it. Im using Excel 2003, by the way. The way Ive been getting round this is by deleting the range and recreating it with the added cells. This is tedious and seems morally wrong. Ive been experimenting with recording Macros to see if I can maintain a range that way, but it doesnt work very well. Here are my examples: 1. Sub Macro1() Range("A1:A4").Select ActiveWorkbook.Names.Add Name:="Range_1", RefersToR1C1:="=Sheet1!R1C1:R4C1" End Sub 1. This one is ok, because its just a group together. I can increase the size by changing the text above from "=Sheet1!R1C1:R4C1" to "=Sheet1!R1C1:R5C1". 2. Sub Macro2() Range("B1,B3,B5,B7").Select Range("B7").Activate ActiveWorkbook.Names.Add Name:="Range_2 ", RefersToR1C1:= _ "=Sheet1!R1C2,Sheet1!R3C2,Sheet1!R5C2,Sheet1!R 7C2" End Sub 2. This one's not so good. If I add Sheet1!R9C2 to it, nothing happens. If I insert some rows between rows three and five the cells in the range remain correct, but the cell references in the VB text dont change. I know I can change these manually, but Im trying to keep things as simple and efficient as possible. Also, a list of even thirty of these in the VB text makes one single very long ugly line, which I dont seem to be able to reduce by putting it on multiple lines. Each one of the cells is individually named and most are quite long names by neccessity. This doesn't help with the reduction of text either. Does anybody know a more efficient way of doing this? My knowledge of VB is very limited, consisting of a few basic instructions, so I would really appreciate some help. Thank you Kind regards Clinton -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named range with multiple groups of cells
Thank you all for your excellent help. Those methods are just what I needed.
Clinton "Clinton W" wrote: Hello, I am attempting to use named ranges consisting of quite a few cells which are not joined in a group. Ive created them by selecting the first cell of the range, then holding down the Ctrl button and selecting the rest, before giving the range a name. Im running into problems when I need to add or remove a cell in one of the ranges. Say for example a range consisting of thirty separate cells; when looking at the list in the €śDefine Name€ť box, and trying to see the cells already in the range along the €śRefers to:€ť line, I can only see about the first five or six. If I click in the line and try to view further along the list, it appears to add extra random cells to it, and if I type in a new cell at the front, it doesnt seem to accept it. Im using Excel 2003, by the way. The way Ive been getting round this is by deleting the range and recreating it with the added cells. This is tedious and seems morally wrong. Ive been experimenting with recording Macros to see if I can maintain a range that way, but it doesnt work very well. Here are my examples: 1. Sub Macro1() Range("A1:A4").Select ActiveWorkbook.Names.Add Name:="Range_1", RefersToR1C1:="=Sheet1!R1C1:R4C1" End Sub 1. This one is ok, because its just a group together. I can increase the size by changing the text above from "=Sheet1!R1C1:R4C1" to "=Sheet1!R1C1:R5C1". 2. Sub Macro2() Range("B1,B3,B5,B7").Select Range("B7").Activate ActiveWorkbook.Names.Add Name:="Range_2 ", RefersToR1C1:= _ "=Sheet1!R1C2,Sheet1!R3C2,Sheet1!R5C2,Sheet1!R 7C2" End Sub 2. This one's not so good. If I add Sheet1!R9C2 to it, nothing happens. If I insert some rows between rows three and five the cells in the range remain correct, but the cell references in the VB text dont change. I know I can change these manually, but Im trying to keep things as simple and efficient as possible. Also, a list of even thirty of these in the VB text makes one single very long ugly line, which I dont seem to be able to reduce by putting it on multiple lines. Each one of the cells is individually named and most are quite long names by neccessity. This doesn't help with the reduction of text either. Does anybody know a more efficient way of doing this? My knowledge of VB is very limited, consisting of a few basic instructions, so I would really appreciate some help. Thank you Kind regards Clinton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Named Range questions | Excel Discussion (Misc queries) | |||
Multiple instances of a named range, not in the list | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
same named range on multiple worksheets? | Excel Discussion (Misc queries) |