Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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.

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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

Help building string for Names.Add RefersTo, pls?
 

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