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

http://www.microsoft.com/office/comm...xp=&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

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))


ExcelMonkey wrote:
>
> This is further to string between Tim Zych and I from December
>
> http://www.microsoft.com/office/comm...xp=&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 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


"ExcelMonkey" <(E-Mail Removed)> wrote in message
news:05F6EA92-44DB-41F0-ACD1-(E-Mail Removed)...
> This is further to string between Tim Zych and I from December
>
>
http://www.microsoft.com/office/comm...xp=&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, _
>
Sheet1!M11,Sheet1!M13,Sheet1!M15,Sheet1!M17,Sheet1!M19,Sheet1!M21,Sheet1!A2"
)
>
> Thanks
>
> EM

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

"Peter T" wrote:

> 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
>
>
> "ExcelMonkey" <(E-Mail Removed)> wrote in message
> news:05F6EA92-44DB-41F0-ACD1-(E-Mail Removed)...
> > This is further to string between Tim Zych and I from December
> >
> >
> http://www.microsoft.com/office/comm...xp=&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, _
> >
> Sheet1!M11,Sheet1!M13,Sheet1!M15,Sheet1!M17,Sheet1!M19,Sheet1!M21,Sheet1!A2"
> )
> >
> > Thanks
> >
> > EM
>
>
>

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

"Dave Peterson" wrote:

> 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))
>
>
> ExcelMonkey wrote:
> >
> > This is further to string between Tim Zych and I from December
> >
> > http://www.microsoft.com/office/comm...xp=&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
>

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

"ExcelMonkey" <(E-Mail Removed)> wrote in message
news:E2DAC30A-F9A9-4428-9510-(E-Mail Removed)...
> 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
>
> "Peter T" wrote:
>
>> 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
>>
>>
>> "ExcelMonkey" <(E-Mail Removed)> wrote in message
>> news:05F6EA92-44DB-41F0-ACD1-(E-Mail Removed)...
>> > This is further to string between Tim Zych and I from December
>> >
>> >
>> http://www.microsoft.com/office/comm...xp=&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, _
>> >
>> Sheet1!M11,Sheet1!M13,Sheet1!M15,Sheet1!M17,Sheet1!M19,Sheet1!M21,Sheet1!A2"
>> )
>> >
>> > Thanks
>> >
>> > EM
>>
>>
>>

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

--
Tim Zych
SF, CA

"ExcelMonkey" <(E-Mail Removed)> wrote in message
news:05F6EA92-44DB-41F0-ACD1-(E-Mail Removed)...
> This is further to string between Tim Zych and I from December
>
> http://www.microsoft.com/office/comm...xp=&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