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