Understood . . . finally! Thanks, Tom.
Ed

"Tom Ogilvy" wrote in message
...
It isn't the length of the name, it is the length of the string that the
name refers to

ex:

Refers to:
=Sheet1!$A$1:$A$4,Sheet1!$B$3:$B$4,Sheet1!$F$11,Sh eet1!$G$9

The length of that string.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
That was it!! Thank you so much! (Although the name was less than 10
characters long, so I'm not real sure how it applied. But it did the
work
just fine, so I'm not complaining at all!)
Ed

"Tom Ogilvy" wrote in message
...
Anytime you select with merged cells, the selection is going to
expand,
so
you should avoid selecting and just work with your range.

As KeepitCool mentioned, using the add method for the names collection
can
get tangled up in the length of a string. It is easier to do

rng.Name = "MyName"

so for you sample code reduce it to:


Range(strCol & "2:" & strCol & _
j).SpecialCells(xlCellTypeVisible).Name = strName


--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
Gee, Tom - I'm sorry you seem to have gotten stuck with "Ed
Clean-Up"
today!

I did a bit more investigating (as I should have before posting to
begin
with!). I am doing this after using the AutoFilter. If I comment
out
the
filter lines, my code to add the name works fine. With the
filtering,
it
errors on the formula.

I also noticed that, due to merged cells that stretch clear across
the
width
of the UsedRange, when I select the Named Range, it is not
constrained
to
just the column, but includes the entire UsedRange. I tried
selecting
just
the single column D and manually creating a range; visually only the
single
column was selected, and the RefersTo code was $D:$D, but when I
selected
the Name it again selected the whole UsedRange.

So does the merged cell problem mean I need to do this a different
way?
Which direction should I look in?

Ed

"Tom Ogilvy" wrote in message
...
Demo'd from the immediate window:

j = 21
strCol = "F"
strName = "ABCD"
ActiveWorkbook.Names.Add Name:=strName, RefersTo:= _
"=" & Range(strCol & "2:" & strCol & _
j).SpecialCells(xlCellTypeVisible).Address

' now lets have a look at the results:

? activeworkbook.Names("ABCD").RefersTo
=Sheet2!$F$2:$F$21

So it all lined up for me with the range refering to the
activesheet
when
the code was run.

--
Regards,
Tom Ogilvy




"Ed" wrote in message
...
Bernie:
Thank you - it looks great! I tried it modified slightly to fit
what
I'm
trying to do, and it didn't work. I'm trying to iterate over a
few
columns
and set this, and run this on a couple of different worksheets,
so
I've
created variables for the last row and for the column
identifier.
The
last
row is Long, and the column identifier is a string (since I've
only
got
a
couple of columns, I used Select Case to change the column
number
back
to
the column letter). My Names.Add code errors out, giving me
"something's
wrong with this formula". Is it do-able the way I'm trying it?
(BTW,
strName has no spaces or other special characters.)

ActiveWorkbook.Names.Add Name:=strName, RefersTo:= _
"=" & Range(strCol & "2:" & strCol &
j).SpecialCells(xlCellTypeVisible).Address

Ed

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
And, really, no need to select:

Range("A1:A10").AutoFilter Field:=1, Criteria1:="Ed"
ActiveWorkbook.Names.Add Name:="NewName", RefersTo:= _
"=" &
Range("A2:A10").SpecialCells(xlCellTypeVisible).Ad dress
Range("A1:A10").AutoFilter

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

You would be better off using built-in Excel functionality,
along
the
lines
of this, where you have a list in cells A1:A10, with the
header
in
row
1,
and you want to find all the cells containing Ed. No need
to
loop,
or
build
a string, or.....

Range("A1:A10").AutoFilter Field:=1, Criteria1:="Ed"
Range("A2:A10").SpecialCells(xlCellTypeVisible).Se lect
ActiveWorkbook.Names.Add Name:="NewName", RefersTo:= _
"=" & Selection.Address
Selection.AutoFilter

HTH,
Bernie
MS Excel MVP


"Ed" wrote in message
...
To add a range, I am using
wb.Names.Add _
Name:=strRng, _
RefersToR1C1:=strAddr

This has worked well, but all my ranges so far have been
single
contiguous
blocks of cells. Now I would like to create a range
consisting
of
several
blocks of non-contiguous (not touching each other) cells.
Specifically,
I
am going to run down a column and check the value of the
data
in
each
cell -
if it meets a certain criterion, I want to add that cell
address
to
the
RefersTo strAddr. I know there will be long stretches of
matching
data,
interrupted by one or two cells that don't belong.

Would it look like "CellRef & CellRef & CellRef", or
"CellRef,
CellRef,
CellRef"?
Or is there an easier way altogether?

Ed