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