Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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). "ExcelMonkey" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit on Range Object
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
characters object limit | Excel Discussion (Misc queries) | |||
returning pivottable object from a range object | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |