Limit on Range Object

[ExcelMonkey]

I have the following line of code:

Set rng = Range("A1,A5")

Is there a limit to the number of addresses I put into this range object?

Thanks

EM

 

[merjet]

The method Union takes up to 30 arguments (Excel 2002).
Sample use: Set rng = Union(Range("A1:A5"), Range("D15"),
Range("L1:L5"))

Hth,
Merjet

 

[ExcelMonkey]

I have a string of addresses which i want to pass to the rng object. They
may not be in contiguous range form. So if I am in a position where I am
dong this:

Set rng = Range("A1,A3,A5,A7,A9,A10 etc")

What are the constraints without taking into account the Union function?

Note I will be using the Union function later but want to know the
constraints with it for the time being.

Thanks

EM

 

[JMB]

Just playing around, I was able to create a range w/over 4200 non-contiguous
cells. Not sure what the upper limit is, but my experience has been
performance really degrades after passing 3000 areas. Some folks suggest
setting a condition to do whatever it is you want to with the range once it
gets to that size, then set your range variable to nothing and continue where
you left off. But that is using a loop and the union function.

In trying to create a string and create the range with Range(strRange) where
strRange is "A1,A3,A5,A7,etc" it seems to me the limiting factor is the
length of the string itself - the longest string I could successfully create
a range with was 254 characters (so the actual limit is probably 255 or 256).

 

[Tim Zych]

255 character limit I believe.

Here is a macro to test:

Sub test2()
On Error GoTo ErrHandler
Dim s As String, s2 As String, i As Long, rng As Range
Const ColRef As String = "A"
' Loop until it fails
For i = 1 To 10000
s = s & Cells(i, ColRef).Address(0, 0)
s2 = s & "," & Cells(i + 1, ColRef).Address(0, 0)
Set rng = Range(s2)
s = s & ","
Next
Exit Sub
ErrHandler:
Debug.Print "Success for range(string(len=" & Len(s) & "))" & vbLf & _
"Failure for range(string(len=" & Len(s2) & "))"
Debug.Print "Success for " & s
Debug.Print "Failure for " & s2
End Sub

 

[Peter T]

A string range address, as per your example, is limited to an absolute max
of 255 characters, though it can fail with a bit less than that, 230 should
be completely safe.

In practical terms the number of areas that can be Union'ed is limited by
system resources, though I guess ultimately there must be a defined limit.
Union'ing above a few hundred areas becomes exponentially slower in a loop
(though there are techniques to significantly speed that up).

Programmatic (though not manual) use of Specialcells fails with 8192+ areas.

Where possible, and normally it is, best to limit use of multiple areas to
between say 100-400 and process batches of multiple areas.

Regards,
Peter T

 

[ExcelMonkey]

Tim, it appears that the code goes to the error handler on the line:

Set rng = Range(s2)

How do you test whether or not that stmt generates an error. I tried
Iserror(Range(s2)) but it did not work.


Thanks

EM

 

[Tim Zych]

Sub test2()
Dim s As String, s2 As String, i As Long, rng As Range
Const ColRef As String = "A"
' Loop until it fails
For i = 1 To 10000
s = s & Cells(i, ColRef).Address(0, 0)
s2 = s & "," & Cells(i + 1, ColRef).Address(0, 0)
On Error Resume Next
Set rng = Range(s2)
If Err.Number <> 0 Then
Debug.Print "Success for range(string(len=" & Len(s) & "))" &
vbLf & _
"Failure for range(string(len=" & Len(s2) & "))"
Debug.Print "Success for " & s
Debug.Print "Failure for " & s2
Exit Sub
End If
On Error GoTo 0
Err.Clear
s = s & ","
Next
End Sub