"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
Address limit is 255 in '97 to 2003, up to 16 areas anywhere on the
sheet.

The limit is not 16 areas, I am currently using 25.

I stick by the 255 address limit but you may have misunderstood what I meant
by "up to 16 areas anywhere on the sheet". The key word is "anywhere", also
although not stated each area with two or more cells below and to the right
of AA10000 with an address like "AA12345:AB12345".

If areas are above and to left of AA10000 and/or are single cell areas, more
than 16 areas can be defined within a 255 address.

The absolute max number of areas defineable within the 255 address limit is
85, albeit highly contived

Sub test1()
Dim i As Long, rng As Range

For i = 1 To 85
s = s & Range("A1:J9")(i).Address(0, 0)
If i < 85 Then
s = s & ","
Else: Exit For
End If
Next

Set rng = Range(s)

Debug.Print Len(s), rng.Areas.count ' 254 85

rng.Select

End Sub

Also, the address limit is higher in Excel 97 than in Excel 2003, see code
below.
I ran the code in both 97 and 2003.

The 255 address limit is well documented for all versions (I think also
2007). The 255 limit also applies to some other things such as refersto
strings in names, however the actual limit may be less. For my purposes I
consider an absolute safe llimit at 230.

I have to dynamically create the string, so I can use On Error to see
whether I have to process in chunks.

No, don't brute force like that. Apart from being unnecessarily slow, with
certain combinations the range can be successfully set with an address
truncated shorter than the original 255+ string, down to 255 or less. I
suspect that is what has made you think you can define more areas in XL97
than in 2003. It's a while since I tested but there is a difference in how
this "truncating" can occur between versions.

Keep track of the address length as you go. When it's known that adding one
more area will exceed 255, process the <=255 chunck.

Add the following to your code to see what's really going on -

Debug.Print rng.Areas.count, Len(rng.Address(0, 0))

Regards,
Peter T