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

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

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

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

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

Tip:
when you want to add a name for a (complex) multiarea range
you may easily run into problems because the str
cannot exceed 255 chars.

By assigning the Name property of the Range Object
you circumvent the problem..

activesheet.Cells.SpecialCells(x).Name = "Gotcha"
or Range(x).Name = "Gotcha2"




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

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.

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