Named range with multiple groups of cells

[Clinton W]

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!R7C2"
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

 

[T. Valko]

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.

 

[Gary Keramidas]

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.

 

[Dave Peterson]

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]

Thank you all for your excellent help. Those methods are just what I needed.
Clinton