Multi-line range name assignments?

[TrippyTom]

I'm trying to setup a named range with multiple non-adjacent columns. But the normal _ sign to extend to the next line isn't working for me. Am I doing something wrong?


Names.Add Name:="formatarea", RefersTo:="=U8:U157,X8:X157,AA8:AA157,AD8:AD157, _
AH8:AH157,AK8:AK157,AN8:AN157,AQ8:AQ157,AT8:AT157, _
AW8:AW157,AZ8:AZ157,BC8:BC157", Visible:=True

Actually, my list is even longer than this, but if I can get the proper syntax for this I can fix the rest of it. Thanks in advance!

[Bob Phillips]

I'm trying to setup a named range with multiple non-adjacent columns. But the normal _ sign to extend to the next line isn't working for me. Am I doing something wrong?


Names.Add Name:="formatarea", RefersTo:="=U8:U157,X8:X157,AA8:AA157,AD8:AD157, _
AH8:AH157,AK8:AK157,AN8:AN157,AQ8:AQ157,AT8:AT157, _
AW8:AW157,AZ8:AZ157,BC8:BC157", Visible:=True

Actually, my list is even longer than this, but if I can get the proper syntax for this I can fix the rest of it. Thanks in advance!

The problem is that it is a string, and you are then trying to embed the continuation character within the string, You have to close the string, add the continuation, then open the string on the next line, circa

X = "a,b,c," & _
"d,e,f," & _
"g,h"

But this is only the start of your problem. As Excel will add the sheet name to each area, it will quickly exceed the 255 character limit.

There is a solution though, you need to turn it around



Dim rng As Range
Set rng = Range("U8:U157,X8:X157,AA8:AA157,AD8:AD157," & _
"AH8:AH157,AK8:AK157,AN8:AN157,AQ8:AQ157," & _
"AT8:AT157,AW8:AW157,AZ8:AZ157,BC8:BC157")
rng.Name = "formatarea"

[TrippyTom]

thanks a bunch! :)

Edit: Hmm, is there a limit to the number of lines I can even add to this? I think my ranges might be too much for Excel to handle. Maybe I can reinterpret it as an intersection of rows 8-157 and columns x,aa,ad,ah,ak.. etc.?

[Bob Phillips]

thanks a bunch! :)

Edit: Hmm, is there a limit to the number of lines I can even add to this? I think my ranges might be too much for Excel to handle. Maybe I can reinterpret it as an intersection of rows 8-157 and columns x,aa,ad,ah,ak.. etc.?

That all works okay doesn't it?

[TrippyTom]

yes, yours works, but when I added ALL my columns to the syntax, it gives me an error like this:

Run-Time Error '1004':
Method 'Range' of object '_Worksheet' failed

Here's my entire code with all the columns I want:


Dim rng As Range
Set rng = Range("U8:U157,X8:X157,AA8:AA157,AD8:AD157,AH8:AH157," & _
"AK8:AK157,AN8:AN157,AQ8:AQ157,AT8:AT157,AW8:AW157,AZ8:AZ157," & _
"BC8:BC157,BG8:BG157,BJ8:BJ157,BM8:BM157,BP8:BP157,BS8:BS157," & _
"BV8:BV157,BY8:BY157,CB8:CB157,CE8:CE157,CH8:CH157,CK8:CK157," & _
"CN8:CN157,CQ8:CQ157,CT8:CT157,CW8:CW157,CZ8:CZ157,DC8:DC157," & _
"DF8:DF157,DI8:DI157,DL8:DL157,DO8:DO157,DR8:DR157,DU8:DU157," & _
"DX8:DX157,EA8:EA157,ED8:ED157,EG8:EG157,EJ8:EJ157,EM8:EM157," & _
"EP8:EP157,ES8:ES157,EV8:EV157,EY8:EY157,FB8:FB157,FE8:FE157," & _
"FH8:FH157,FK8:FK157")
rng.Name = "formatarea"

I narrowed it down to 4 lines. If it goes beyond 4 lines it gives me the error I mentioned above.

[TrippyTom]

I'm thinking I need to use some form of INTERSECT instead... Can anyone help?

[BDavidson]

This seems to work for me.



Dim rng As Range
Set rng = Range("U8:U157,X8:X157,AA8:AA157,AD8:AD157,AH8:AH157," & _
"AK8:AK157,AN8:AN157,AQ8:AQ157,AT8:AT157")
Set rng = Union(rng, Range("AW8:AW157,AZ8:AZ157," & _
"BC8:BC157,BG8:BG157,BJ8:BJ157,BM8:BM157,BP8:BP157,BS8:BS157," & _
"BV8:BV157,BY8:BY157,CB8:CB157,CE8:CE157,CH8:CH157,CK8:CK157"))
Set rng = Union(rng, Range("CN8:CN157,CQ8:CQ157,CT8:CT157,CW8:CW157,CZ8:CZ157,DC8:DC157," & _
"DF8:DF157,DI8:DI157,DL8:DL157,DO8:DO157,DR8:DR157,DU8:DU157," & _
"DX8:DX157,EA8:EA157,ED8:ED157,EG8:EG157,EJ8:EJ157,EM8:EM157"))
Set rng = Union(rng, Range("EP8:EP157,ES8:ES157,EV8:EV157,EY8:EY157,FB8:FB157,FE8:FE157," & _
"FH8:FH157,FK8:FK157"))
rng.Name = "formatarea"

[Bob Phillips]

I'm thinking I need to use some form of INTERSECT instead... Can anyone help?

Bit impatient aren't we. This is a volunteer forum, not a help desk.

You don't need intersect, you just need to understand what is going on



Dim rng As Range
Set rng = Range("U8:U157,X8:X157,AA8:AA157,AD8:AD157,AH8:AH157," & _
"AK8:AK157,AN8:AN157,AQ8:AQ157,AT8:AT157,AW8:AW157,AZ8:AZ157," & _
"BC8:BC157,BG8:BG157,BJ8:BJ157,BM8:BM157,BP8:BP157,BS8:BS157")
Set rng = Union(rng, Range("BV8:BV157,BY8:BY157,CB8:CB157,CE8:CE157,CH8:CH157,CK8:CK157," & _
"CN8:CN157,CQ8:CQ157,CT8:CT157,CW8:CW157,CZ8:CZ157,DC8:DC157," & _
"DF8:DF157,DI8:DI157,DL8:DL157,DO8:DO157,DR8:DR157,DU8:DU157"))
Set rng = Union(rng, Range("DX8:DX157,EA8:EA157,ED8:ED157,EG8:EG157,EJ8:EJ157,EM8:EM157," & _
"EP8:EP157,ES8:ES157,EV8:EV157,EY8:EY157,FB8:FB157,FE8:FE157," & _
"FH8:FH157,FK8:FK157"))
rng.Name = "formatarea"

[TrippyTom]

No, I don't think I was impatient at all. Perhaps you didn't realize it but I was narrowing down the error and trying to come up with alternative solutions to my question during the whole time of my posting. And there was around 20 minutes to an hour between each of my posts.

I don't expect any forum to be a "help desk". However, given the nature of this forum, I do expect it to be a sharing of ideas and variations of ways to tackle questions people post. Maybe if I had used some emoticons you wouldn't have taken me the wrong way. :thumb :eek: :hi: :dunno :yes :rotlaugh: :p

In regard to your solution, can you explain why I would have to split it up like this? Is there always a 255 character limit per line or something? I haven't seen this limitation in any of the books or cds I have or even in the VBA help. I'm probably just looking in the wrong places. :(

This is my version of the Intersect method, but the selection seems to stop at column CK. I presume it relates to the limitation somehow.


Dim formatarea As Range
formatarea = Intersect(Range("U8:FK157"), Range("U:U"), Range("X:X"), Range("AA:AA"), _
Range("AD:AD"), Range("AH:AH"), Range("AK:AK"), Range("AN:AN"), Range("AQ:AQ"), Range("AT:AT"), _
Range("AW:AW"), Range("AZ:AZ"), Range("BC:BC"), Range("BG:BG"), Range("BJ:BJ"), Range("BM:BM"), _
Range("BP:BP"), Range("BS:BS"), Range("BV:BV"), Range("BY:BY"), Range("CB:CB"), Range("CE:CE"), _
Range("CH:CH"), Range("CK:CK"), Range("CN:CN"), Range("CQ:CQ"), Range("CT:CT"), Range("CW,CW"), _
Range("CZ:CZ"), Range("DC:DC"), Range("DF:DF"), Range("DI:DI"), Range("DL:DL"), Range("DO:DO"), _
Range("DR:DR"), Range("DU,DU"), Range("DX:DX"), Range("EA:EA"), Range("ED:ED"), Range("EG:EG"), _
Range("EJ:EJ"), Range("EM:EM"), Range("EP:EP"), Range("ES:ES"), Range("EV:EV"), Range("EY:EY"), _
Range("FB:FB"), Range("FE:FE"), Range("FH:FH"), Range("FK:FK"))
formatarea.Select

[BDavidson]

Tom, I'm not aware of any limitations either. Did you try my code? It worked okay for me.

[TrippyTom]

ok, i feel dumb now. As usual, it was user error. I hadn't finished entering in all the columns in my code. :rofl: And I was wondering why the heck it wasn't going through all the columns! Until computers can read my mind, I will continually beat my head against a wall from user error. :banghead:

Thanks for your input guys. I came here to learn things and now I have.

edit: Yes, Bdavidson.. in fact, it was both examples that made me look twice at my code. I had more columns beyond FK that I forgot to put in. Thanks again :clap:

[Bob Phillips]

ok, i feel dumb now. As usual, it was user error. I hadn't finished entering in all the columns in my code. :rofl: And I was wondering why the heck it wasn't going through all the columns! Until computers can read my mind, I will continually beat my head against a wall from user error. :banghead:

Thanks for your input guys. I came here to learn things and now I have.

edit: Yes, Bdavidson.. in fact, it was both examples that made me look twice at my code. I had more columns beyond FK that I forgot to put in. Thanks again :clap:

Your way off course with intersect.

Intersect on two ranges returns a range where they intersect, hence the name.

So Intersect(Range("U8:FK157"), Range("U:U")) returns the range that intersects, namely U8:U157.

Similarly, Intersect(Range(("U:U"), Range("X:X")) returns an empty range.

.

[TrippyTom]

Doesn't Intersect allow for multiple ranges? The vba help file makes it look that way.

Returns a Range (http://mk:@msitstore:J:apps_pubmsop2k60msopkgProgram%20FilesMicrosoft%20OfficeOffi ce1033vbaxl9.chm::/html/xlobjRange.htm) object that represents the rectangular intersection of two or more ranges.
Syntax

expression.Intersect(Arg1, Arg2, ...)

expression Optional. An expression that returns an Application object.

Arg1, Arg2, ... Required Range. The intersecting ranges. At least two Range objects must be specified.
If so, why would that not work then if my first range covers the general area and the rest are just the columns within that area? Seems to me the intersection would be the resulting columns in those rows. No?

I still had to use the "Set rng=" method, however, because VBA wouldn't allow me to use that many parameters in the Intersect function. So thanks for putting up with my "impatience". :bow:

[Bob Phillips]

Doesn't Intersect allow for multiple ranges? The vba help file makes it look that way.

Yes of course it does, but it is not appropriate here, not because of multiple ranges or the lack thereof, but because it returns the intersect of two ranges, which is great if you are trying to test whether two ranges do intersect, but that is not what you are trying to do. You are trying to overcome inherent limitations in setting a range, which means building up a range.

Or to be blunt, why do


Set rng = Intersect(Range("U8:FK157"), Range("U:U"))

when you can more simply do


Set rng = Range("U8:U157")

The former is just perverse.


If so, why would that not work then if my first range covers the general area and the rest are just the columns within that area? Seems to me the intersection would be the resulting columns in those rows. No?

It might just as well work, but travelling from New York to Boston via San Francisco gets you to Boston just as going direct does, but you wouldn't go that way, would you?


I still had to use the "Set rng=" method, however, because VBA wouldn't allow me to use that many parameters in the Intersect function.

Precisely, it won't with the Union method either, that is the work-around I showed up at the start.

[TrippyTom]

I was simply trying to find ways to make my selection work. Since the direct approach wasn't working, I tried to find another way. I still don't know why you have to "build up" ranges like this. Can you explain why excel makes us set massive ranges in this way?

That's all I really wanna know.

[Bob Phillips]

I was simply trying to find ways to make my selection work. Since the direct approach wasn't working, I tried to find another way. I still don't know why you have to "build up" ranges like this. Can you explain why excel makes us set massive ranges in this way?

That's all I really wanna know.

No I can't say that I know why it is this way, but like all applications, the designers make some assumptions. Excel names are meant for use in Excel, and the developers used a string to hold the Refersto value. It is just that strings in Excel often are limited to 255 characters, and you hit this particular barrier even when you tried to do it from VBA, as it was essentially calling the Excel processes to add that name.

You can actually go and edit that refersto value and increase it, so 255 is not an absolute limit, just one that applies when creating the name that way.

The method I showed you for some reason uses another process, one which doesn't pre-prend the sheet name at that initial point, so it can build much bigger refersto strings. Don't ask me why, I didn't build Excel, it just is.

These are the vagaries of Excel, just like any other product, you learn and absorb.

[TrippyTom]

Ok XLD, that makes sense.. enough to satisfy my curiosity anyway. Thanks for the explanation.

:beerchug: