"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
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".

The length is not limited to 255 in Excel 97.
Run the example I posted to see this.


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

I believe that the limit is the string length, not the nimber of areas.

Absolutely, that's what I have been saying all along.

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.

The documentation is incorrect.

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.

It is not slow.
How can one shorten the string?
All areas are two consecutive cells, up to 4 per row, every Nth row.

Keep track of the how much you are increasing the address by in each loop.
If len(address) + len(to be added next next loop) 255 then do that chunk.
Might need to subtract a comma.


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.

I stated that the string can be longer.
Run my exanple in Excek 97 to see this.

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.

That does not work because the limit may be different in each version of
Excel.

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

I've already done that.


I should have fully tested in Excel 97, apologies for not having done so.
Indeed it does appear possible to make a range with an address up to about
300. However it is not possible to return the address directly, or rather
the return address is truncated to 255.

I wonder if it's not a bug in XL 97, at the very least an anomaly.

Sub test()
Dim s As String, i As Long, k As Long, dw As Long
Dim rng As Range

For dw = 240 To 400 Step 10
i = 0: k = 0: s = ""
Do While Len(s) < dw
i = i + 2: k = k + 1
s = s & Cells(i, 1).Address(0, 0) & ","
Loop

s = Left(s, Len(s) - 1)
Set rng = Range(s)
' no point to return the address, it'll truncate at 255

MsgBox k & " " & rng.Areas.Count, , Len(s)

Next
End Sub

At around the next loop after 310-320 attempting to assign the long address
crashes my xl97, or it might have been the attempt to read .areas.count,
difficult to tell. Either way it points more to a bug than an anomaly.

Despite the apparent ability to be able set a range with an address of 255+
to 300 or even a bit more in xl97 I really would consider 255 as the limit
for all versions.

Regards,
Peter T