Help building string for Names.Add RefersTo, pls?

[Ed]

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 Deitrick]

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).Select
ActiveWorkbook.Names.Add Name:="NewName", RefersTo:= _
"=" & Selection.Address
Selection.AutoFilter

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

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).Address
Range("A1:A10").AutoFilter

HTH,
Bernie
MS Excel MVP

 

[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

 

[Tom Ogilvy]

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.

 

[keepITcool]

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 :

 

[Ed]

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]

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

 

[Ed]

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]

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,Sheet1!$G$9

The length of that string.

 

[Ed]

Understood . . . finally! Thanks, Tom.
Ed

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,Sheet1!$G$9

The length of that string.

--
Regards,
Tom Ogilvy

expand,