Avoiding Errors when Loading Large Number of Cell Addresses into R

[ExcelMonkey]

This is further to string between Tim Zych and I from December

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

The Sub below loades cell addresses into a range object and pulls the
contiguous addresses together via a Union function. Now I am trying to load
a large number of cell addresses from an array into the range object. The
array is large and breaches the limit on the # of characters that I can put
into the Range Object in VBA. For example if I try to load the example below
the sub into the range object, I will get an error message. Note only the
first address Sheet1!A1 and the last cell address Sheet1!A2 are contigous

QUESTION:
Is it possible to keep creating additional range objects as needed to avoid
the error? And if I am going to have to split these up between multiple
range objects, I want to ensure that all contiguous cells are in the same
range object and then fed into the Union Function. In the example provided,
if multiple range objects are created, then the last cell address (Sheet1!A2)
will not be joined with the first cell address (Sheet1!A1) via the union
function. I am assuming I would need a routine which checks all th range
objects prior to the union function being applied to each range object.

Or is there a better way to do this relative to the multiple range object
approach?

'******************************
Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4")
Set rngUnion = rng.Areas(1)
For Each rngArea In rng.Areas
Set rngUnion = Union(rngUnion, rngArea)
Next
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next
End Sub
'**********************************88

Range("Sheet1!A1,Sheet1!A3,Sheet1!A5,Sheet1!A7,Sheet1!A9,Sheet1!A11,Sheet1!A13,Sheet1!A15,Sheet1! _
Sheet1!A17,Sheet1!A19,Sheet1!A21,Sheet1!C1,Sheet1!C3,Sheet1!C5,Sheet1!C7,Sheet1!C9,Sheet1!C11, _
Sheet1!C13,Sheet1!C15,
Sheet1!C17,Sheet1!C19,Sheet1!C21,Sheet1!E1,Sheet1!E3,Sheet1, Sheet1!E5, _
Sheet1!E7,Sheet1!E9,Sheet1!E11,Sheet1!E13,Sheet1!E15,Sheet1!E17,Sheet1!E19,Sheet1!E21,Sheet1!G1, _
Sheet1!G3,Sheet1!G5,Sheet1!G7,Sheet1!G9,Sheet1!G11,Sheet1!G13,
Sheet1!G15,Sheet1!G17,Sheet1!G19, _
Sheet1!G21,Sheet1!I1,Sheet1!I3,Sheet1!I5,Sheet1!I7,Sheet1!I9,Sheet1!I11,Sheet1!I13,Sheet1!I15,Sheet1!I17, _
Sheet1!I19,Sheet1!I21,Sheet1!K1,Sheet1!K3,Sheet1!K5,Sheet1!K7,Sheet1!K9,Sheet1!K11,Sheet1!K13, _
Sheet1!K15,Sheet1!K17,Sheet1!K19,Sheet1!K21,Sheet1!M1,Sheet1!M3,Sheet1!M5,Sheet1!M7,Sheet1!M9, _
Sheet1!M11,Sheet1!M13,Sheet1!M15,Sheet1!M17,Sheet1!M19,Sheet1!M21,Sheet1!A2")

Thanks

EM

 

[Dave Peterson]

I'd replace this line:
Set rng = Range("Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4")
with
with worksheets("sheet1")
Set rng = .Range("A1:A4")
end with

The start of that other expression could be:

with worksheets("Sheet1")
set rng1 = .range("a1,a3,a5,a7,a9,a11,a13,a15,a17,a19,a21")
set rng2 = .range("c1,c3,c5,c7,c9,c11,c13,c15,c17,c19,c21")
...
End With
set rngAll = union(rng1, rng2, ..., rngN)


If the range were nice, you may be able to do something like:
with worksheets("Sheet1")
set rng1 = .range("a1,c1,e1,g1,i1,l1,k1,m1")
set rng2 = .range("a1,a3,a5,a7,a9,a11,a13,a15,a17,a19,a21")
End with
set rngAll = intersect(rng1.entirecolumn, rng2.entirerow)

And you could keep building...
set rngAll = union(.range("a2"), intersect(rng1.entirecolumn, rng2.entirerow))

 

[Peter T]

I really don't follow what you are trying to do other than you need to deal
with the max 255 string address limit. Just for ideas -

Sub test()
Dim a As Long, b As Long
Dim j As Long, k As Long
Dim s As String, t As String
Dim rng As Range
Const n As Long = 230

For j = 1 To 200 Step 2
For k = 1 To 20 Step 2
s = s & "Sheet1!" & Cells(j, k).Address(0, 0) & ","
Next
Next
s = Left(s, Len(s) - 1)
MsgBox Left(s, 500) & vbCr & vbCr & Right(s, 500), , "address " & Len(s)

s = Replace(s, "Sheet1!", "")
MsgBox Left(s, 500) & vbCr & vbCr & Right(s, 500), , "address " & Len(s)
b = 0
With Worksheets("Sheet1")
Do
a = b + 1
b = InStr(a + n, s, ",")
If b = 0 Then
t = Mid(s, a, n)
Else
t = Mid(s, a, b - a)
End If

If a = 1 Then
Set rng = .Range(t)
Else
Set rng = Union(rng, .Range(t))
End If
Loop Until b = 0
End With

Debug.Print rng.Cells.Count, rng.Areas.Count
rng.parent.activate
rng.Select
End Sub

I couldn't make sense of the address you posted hence the 'built' address in
the demo

Regards,
Peter T

This is further to string between Tim Zych and I from December

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

The Sub below loades cell addresses into a range object and pulls the
contiguous addresses together via a Union function. Now I am trying to load
a large number of cell addresses from an array into the range object. The
array is large and breaches the limit on the # of characters that I can put
into the Range Object in VBA. For example if I try to load the example below
the sub into the range object, I will get an error message. Note only the
first address Sheet1!A1 and the last cell address Sheet1!A2 are contigous

QUESTION:
Is it possible to keep creating additional range objects as needed to avoid
the error? And if I am going to have to split these up between multiple
range objects, I want to ensure that all contiguous cells are in the same
range object and then fed into the Union Function. In the example provided,
if multiple range objects are created, then the last cell address (Sheet1!A2)
will not be joined with the first cell address (Sheet1!A1) via the union
function. I am assuming I would need a routine which checks all th range
objects prior to the union function being applied to each range object.

Or is there a better way to do this relative to the multiple range object
approach?

'******************************
Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4")
Set rngUnion = rng.Areas(1)
For Each rngArea In rng.Areas
Set rngUnion = Union(rngUnion, rngArea)
Next
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next
End Sub
'**********************************88

Range("Sheet1!A1,Sheet1!A3,Sheet1!A5,Sheet1!A7,Sheet1!A9,Sheet1!A11,Sheet1!A
13,Sheet1!A15,Sheet1! _Sheet1!A17,Sheet1!A19,Sheet1!A21,Sheet1!C1,Sheet1!C3,Sheet1!C5,Sheet1!C7,She
et1!C9,Sheet1!C11, _

Sheet1!C13,Sheet1!C15,
Sheet1!C17,Sheet1!C19,Sheet1!C21,Sheet1!E1,Sheet1!E3,Sheet1, Sheet1!E5, _

Sheet1!E7,Sheet1!E9,Sheet1!E11,Sheet1!E13,Sheet1!E15,Sheet1!E17,Sheet1!E19,S
heet1!E21,Sheet1!G1, _

Sheet1!G3,Sheet1!G5,Sheet1!G7,Sheet1!G9,Sheet1!G11,Sheet1!G13,
Sheet1!G15,Sheet1!G17,Sheet1!G19, _

Sheet1!G21,Sheet1!I1,Sheet1!I3,Sheet1!I5,Sheet1!I7,Sheet1!I9,Sheet1!I11,Shee
t1!I13,Sheet1!I15,Sheet1!I17, _Sheet1!I19,Sheet1!I21,Sheet1!K1,Sheet1!K3,Sheet1!K5,Sheet1!K7,Sheet1!K9,Shee
t1!K11,Sheet1!K13, _Sheet1!K15,Sheet1!K17,Sheet1!K19,Sheet1!K21,Sheet1!M1,Sheet1!M3,Sheet1!M5,Sh
eet1!M7,Sheet1!M9, _

 

[ExcelMonkey]

The range I put in was simply made up to reflect the scenario where I do not
get any contigous cells prior to the range object error. When they are
contigous, the union function concatenates them. As I do not know ahead of
time if the cells that are in my array are contiguous, sending the entire
array to the range object may trigger an error. Think of a scenario where I
get:

A1, C1, E1, G1, ........................A2, A3, A4, A5

The range object will produce an error when the char limit reaches 255 or
whatever it is. So I need to know how to handle the addresses that do not
fit into the first range object AND how to concatenate them with other
potential contigous cells in other range objects (if multiple range objects
is the way to go here).

When this is all said and done, I want to be able to dump the revised
addresses (what was in the original array - concatenated to reflect ranges)
into a final array. I might end up with output that looks like this

A1:A5, C1, E1, G1

Is that clearer?

EM

 

[ExcelMonkey]

As per my response to Peter, I don't know how many cell addresses I will have
ahead of time or if they will be contiguous so in a scenario where I have:

A1, C1, E1, G1,...............A2

I may breach the 255 char constraint. So would I get around this if my goal
is to restate the cell address in their contigous forms:

A1:A2, C1, E1, G1.....

Thanks

EM

 

[Peter T]

It's simple enough to test in advance if the address exceeds 255, if it does
it will fail, in which case try the demo I posted.

Note - if you asign a range to an address with adjacent cell address's, the
range will not 'join' what appear to be contiguous cells, ie "A1, A2" will
be two distinct albeit adjacent areas. You can though do this -

Set r = Range("A1,A2")
MsgBox r.Address(0, 0), , r.Areas.Count
Set r = Union(r, r)
MsgBox r.Address(0, 0), , r.Areas.Count ' note the : vs the ,

When it comes to returning the address of a potentially over large address,
loop each area in the range, adding it's address after inserting a comma (a
bit like I did in the demo).

Regards,
Peter T

 

[Tim Zych]

If you have a huge string of addresses, you could use the Split function to
create an array, loop through the array and build the range object.

Dim RngAddr As String, i As Integer, varRngAddr As Variant
Dim rngUnion As Range
RngAddr =
"Sheet1!A17,Sheet1!A19,Sheet1!A21,Sheet1!C1,Sheet1!C3,Sheet1!C5,Sheet1!C7,Sheet1!C9,Sheet1!C11"
varRngAddr = Split(RngAddr, ",")
For i = LBound(varRngAddr) To UBound(varRngAddr)
If rngUnion Is Nothing Then
Set rngUnion = Range(varRngAddr(i))
Else
Set rngUnion = Union(rngUnion, Range(varRngAddr(i)))
End If
Next