Range collection
 

Hello,

Is ther a limit to the # of cells one can use in a range collection? IE:

For Each rCell In
Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63")

rCell.Value = rData(i)

i = i + 1

Next

This does not work...

Ernst.

Range collection
 

I recommend instead managing that massive list in a String array, and
for j = ubound(strAddr) to 1
range(strAddr(j)).Value = ...

As to "This does not work" a possible culprit is that line width, which
is excessive. If anyone reads your message in a web browser it will
possibly be unpleasant to view. When you have a string that long, use
the underscore(_) for line continuation; you have the ampersand(&)
operator if you really need that long of a string. A reasonable rule of
thumb with long strings would be to line-continue after 60-70
characters.

Excel limits are at
http://office.microsoft.com/en-us/as...992911033.aspx
though I see and suspect no limit on range sizes, per se. You're
perfectly free to put a value in IV65536 and Select All so a range limit
is unlikely. Unless I'm mistaken, of course :)

Range collection
 

Hi. Just a another guess, but Excel may need to know what "Area" of your
range you are referring to.
In general, C17:Q17 is Area 1, S17:T17 is area 2, etc.
Not tested, but here's the general idea:

Dim BigArea As Range
Dim Rng As Range
Dim Cell As Range

Set BigArea = Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7") '...etc

For Each Rng In BigArea.Areas
For Each Cell In Rng.Cells
Cell.Value = rData(i)
i = i + 1
Next Cell
Next Rng

HTH
--
Dana DeLouis
Win XP & Office 2003


"Ernst Guckel" wrote in message
...
Hello,

Is ther a limit to the # of cells one can use in a range collection? IE:

For Each rCell In
Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63")

rCell.Value = rData(i)

i = i + 1

Next

This does not work...

Ernst.

Range collection
 

How does it not work?

Also, you may be better off recognizing and leveraging the patterns in
the layout of the various regions.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello,

Is ther a limit to the # of cells one can use in a range collection? IE:

For Each rCell In
Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27,AH21:AO27,AQ21:AR27,C31 :Q37,S31:T37,V31:AC37,AH31:AO37,AQ31:AR37,C41:Q47, S41:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T 57,V51:AC57,AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V6 2:AC63,AH62:AO63,AQ62:AR63")

rCell.Value = rData(i)

i = i + 1

Next

This does not work...

Ernst.

Range collection
 

This worked fine for me
(I broke up you string just so it would paste better into the post)

Sub ABC()
Dim rData(1 To 2000) As Long
For i = 1 To 2000
rData(i) = Int(Rnd() * 10000 + 1)
Next
i = 1
For Each rCell In Range("C17:Q17,S17:T17,V17:AC17" & _
",AH17:AO17,AQ17:AR17,C21:Q27,S21:T27,V21:AC27 ," & _
"AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37, " & _
"AH31:AO37,AQ31:AR37,C41:Q47,S41:T47,V41:AC47, " & _
"AH41:AO47,AQ41:AR47,C51:Q57,S51:T57,V51:AC57, " & _
"AH51:AO57,AQ51:AR57,C62:Q63,S62:T63,V62:AC63, " & _
"AH62:AO63,AQ62:AR63")
rCell.Value = rData(i)
i = i + 1
Next
Debug.Print i
End Sub

So this version worded as well also:

Sub ABC()
Dim rData(1 To 2000) As Long
For i = 1 To 2000
rData(i) = Int(Rnd() * 10000 + 1)
Next
i = 1
For Each rCell In
Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27
,AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37,AH31 :AO37,AQ31:AR37,C41:Q47,S4
1:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T57 ,V51:AC57,AH51:AO57,AQ51:A
R57,C62:Q63,S62:T63,V62:AC63,AH62:AO63,AQ62:AR63")
rCell.Value = rData(i)
i = i + 1
Next
Debug.Print i
End Sub

--
Regards,
Tom Ogilvy

"Ernst Guckel" wrote in message
...
Hello,

Is ther a limit to the # of cells one can use in a range collection?
IE:

For Each rCell In

Range("C17:Q17,S17:T17,V17:AC17,AH17:AO17,AQ17:AR1 7,C21:Q27,S21:T27,V21:AC27
,AH21:AO27,AQ21:AR27,C31:Q37,S31:T37,V31:AC37,AH31 :AO37,AQ31:AR37,C41:Q47,S4
1:T47,V41:AC47,AH41:AO47,AQ41:AR47,C51:Q57,S51:T57 ,V51:AC57,AH51:AO57,AQ51:A
R57,C62:Q63,S62:T63,V62:AC63,AH62:AO63,AQ62:AR63")

rCell.Value = rData(i)

i = i + 1

Next

This does not work...

Ernst.