Limit on Range Object
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
--
Tim Zych
SF, CA
"ExcelMonkey" wrote in message
...
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" wrote:
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
--
Tim Zych
SF, CA
"ExcelMonkey" wrote in message
...
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
"merjet" wrote:
The method Union takes up to 30 arguments (Excel 2002).
Sample use: Set rng = Union(Range("A1:A5"), Range("D1:D5"),
Range("L1:L5"))
Hth,
Merjet
|