Is there an upper limit to the number of non-contiguous cells Excel can hold
in a range object? The Address property fatigues at 256 characters, but I
could get no indication from Cells.Count that there is any upper limit
(perhaps I ran out of patience)


1000 increment 00:00:03 total time = 00:00:03
2000 increment 00:00:25 total time = 00:00:27
3000 increment 00:01:05 total time = 00:01:32
4000 increment 00:02:06 total time = 00:03:38
5000 increment 00:03:10 total time = 00:06:48
....


'Code for this test :
'(I didn't let it run all the way to the 8,838,608 cells that
'could have been added on just one worksheet of course)

Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range

On Error GoTo Err_
Set AllRange = Cells(2, 1)

For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
' some code to measure progress
End If
Next i
Next j

Exit_Sub:
Exit Sub

Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub

Hi Bill,

According to MSKB # 832293

http://support.microsoft.com/default...b;en-us;832293

'==========================================
Excel only supports a maximum of 8,192 non-contiguous cells through VBA
macros.

Typically, if you try to manually select more than 8,192 non-contiguous
cells, you receive the following error message:
The selection is too large.

However, when you use a VBA macro to make the same or a similar selection,
no error message is raised and no error code is generated that can be
captured through an error handler.

WORKAROUND
To work around this behavior, you may want to create a looping structure in
your VBA macro that handles less than the maximum 8,192 cells.

STATUS
This behavior is by design.
..==========================================

Whilst this KB article refers to problems related to the SpecialCells
method, I believe that wider context suggested by the quoted wording
pertains and that this represents a VBA limit.


---
Regards,
Norman



"William Benson" wrote in message
...
Is there an upper limit to the number of non-contiguous cells Excel can
hold in a range object? The Address property fatigues at 256 characters,
but I could get no indication from Cells.Count that there is any upper
limit (perhaps I ran out of patience)


1000 increment 00:00:03 total time = 00:00:03
2000 increment 00:00:25 total time = 00:00:27
3000 increment 00:01:05 total time = 00:01:32
4000 increment 00:02:06 total time = 00:03:38
5000 increment 00:03:10 total time = 00:06:48
...


'Code for this test :
'(I didn't let it run all the way to the 8,838,608 cells that
'could have been added on just one worksheet of course)

Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range

On Error GoTo Err_
Set AllRange = Cells(2, 1)

For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
' some code to measure progress
End If
Next i
Next j

Exit_Sub:
Exit Sub

Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub

Here is the continuation of the macro ... no errors yet:

500 increment 00:00:01 total time = 00:00:01
1000 increment 00:00:03 total time = 00:00:04
1500 increment 00:00:08 total time = 00:00:12
2000 increment 00:00:16 total time = 00:00:28
2500 increment 00:00:25 total time = 00:00:53
3000 increment 00:00:39 total time = 00:01:32
3500 increment 00:00:56 total time = 00:02:28
4000 increment 00:01:13 total time = 00:03:41
4500 increment 00:01:31 total time = 00:05:12
5000 increment 00:01:55 total time = 00:07:07
5500 increment 00:02:49 total time = 00:09:56
6000 increment 00:03:08 total time = 00:13:04
6500 increment 00:03:38 total time = 00:16:42
7000 increment 00:04:38 total time = 00:21:20
7500 increment 00:05:10 total time = 00:26:30
8000 increment 00:05:58 total time = 00:32:28
8500 increment 00:06:37 total time = 00:39:05
9000 increment 00:07:36 total time = 00:46:41
9500 increment 00:08:28 total time = 00:55:09
10000 increment 00:09:19 total time = 01:04:28
10500 increment 00:10:25 total time = 01:14:53
11000 increment 00:11:42 total time = 01:26:35
11500 increment 00:12:37 total time = 01:39:12
12000 increment 00:13:42 total time = 01:52:54
12500 increment 00:14:31 total time = 02:07:25
13000 increment 00:16:06 total time = 02:23:31
13500 increment 00:17:06 total time = 02:40:37
14000 increment 00:18:31 total time = 02:59:08
14500 increment 00:19:50 total time = 03:18:58
15000 increment 00:20:57 total time = 03:39:55
15500 increment 00:22:40 total time = 04:02:35
16000 increment 00:24:07 total time = 04:26:42
16500 increment 00:25:19 total time = 04:52:01
17000 increment 00:26:48 total time = 05:18:49
17500 increment 00:28:41 total time = 05:47:30
18000 increment 00:30:15 total time = 06:17:45
18500 increment 00:31:51 total time = 06:49:36

"Norman Jones" wrote in message
...
Hi Bill,

According to MSKB # 832293

http://support.microsoft.com/default...b;en-us;832293

'==========================================
Excel only supports a maximum of 8,192 non-contiguous cells through VBA
macros.

Typically, if you try to manually select more than 8,192 non-contiguous
cells, you receive the following error message:
The selection is too large.

However, when you use a VBA macro to make the same or a similar selection,
no error message is raised and no error code is generated that can be
captured through an error handler.

WORKAROUND
To work around this behavior, you may want to create a looping structure
in your VBA macro that handles less than the maximum 8,192 cells.

STATUS
This behavior is by design.
.==========================================

Whilst this KB article refers to problems related to the SpecialCells
method, I believe that wider context suggested by the quoted wording
pertains and that this represents a VBA limit.


---
Regards,
Norman



"William Benson" wrote in message
...
Is there an upper limit to the number of non-contiguous cells Excel can
hold in a range object? The Address property fatigues at 256 characters,
but I could get no indication from Cells.Count that there is any upper
limit (perhaps I ran out of patience)


1000 increment 00:00:03 total time = 00:00:03
2000 increment 00:00:25 total time = 00:00:27
3000 increment 00:01:05 total time = 00:01:32
4000 increment 00:02:06 total time = 00:03:38
5000 increment 00:03:10 total time = 00:06:48
...


'Code for this test :
'(I didn't let it run all the way to the 8,838,608 cells that
'could have been added on just one worksheet of course)

Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range

On Error GoTo Err_
Set AllRange = Cells(2, 1)

For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
' some code to measure progress
End If
Next i
Next j

Exit_Sub:
Exit Sub

Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub

yes.

a multiarea range object can hold
a maximum of 8192 areas.


Fastest way to test is using SpecialCells.

in VBA there's a bug in the specialcells method.
IF you do a search and the speciallcells has
8192 areas to return all is ok. BUT 1 area more
and it returns 1 area equal to the used range.
(NO warning, NO TRAPPABLE error)


Slowest way to test is using Union.
(at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas.. as demonstrated by your code.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


William Benson wrote :

Is there an upper limit to the number of non-contiguous cells Excel
can hold in a range object? The Address property fatigues at 256
characters, but I could get no indication from Cells.Count that there
is any upper limit (perhaps I ran out of patience)


1000 increment 00:00:03 total time = 00:00:03
2000 increment 00:00:25 total time = 00:00:27
3000 increment 00:01:05 total time = 00:01:32
4000 increment 00:02:06 total time = 00:03:38
5000 increment 00:03:10 total time = 00:06:48
...


'Code for this test :
'(I didn't let it run all the way to the 8,838,608 cells that
'could have been added on just one worksheet of course)

Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range

On Error GoTo Err_
Set AllRange = Cells(2, 1)

For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
' some code to measure progress
End If
Next i
Next j

Exit_Sub:
Exit Sub

Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub

Hi KeepITcool,

at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas..

Just curiosity, do you see any market potential for a routine (ActiveX) that
might substantially overcome this, for say up to 8000 areas.

Regards,
Peter T

pmbthornton at gmail com

I wouldnt know. But somehow I doubt it.
since vba is non compiled there's a licensing problem too.

I AM interested to see the code ..
which goes back to my struggle
for an efficient 'OUTERSECT' routine.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

Hi KeepITcool,

at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas..

Just curiosity, do you see any market potential for a routine
(ActiveX) that might substantially overcome this, for say up to 8000
areas.

Regards,
Peter T

pmbthornton at gmail com

It's a small part of a project in development. Unfortunately for the time
being the union bit is under wraps in VB6.

A rough idea of union timings with single cell discontiguous areas, slightly
longer if all multicells.

areas sec's
500 0.070
1000 0.187
2000 0.535
4000 1.805
8000 6.550

Regards,
Peter T


"keepITcool" wrote in message
.com...

I wouldnt know. But somehow I doubt it.
since vba is non compiled there's a licensing problem too.

I AM interested to see the code ..
which goes back to my struggle
for an efficient 'OUTERSECT' routine.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

Hi KeepITcool,

at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas..

Just curiosity, do you see any market potential for a routine
(ActiveX) that might substantially overcome this, for say up to 8000
areas.

Regards,
Peter T

pmbthornton at gmail com

Sorry to post this twice, but I should have replied to the deepest message
in the thread:

I have been able to add substantially more than 8000 areas ... unless you
doubt the integrity of my code: Remember this is using Cells.Count so it is
definitely accurate. Whether I am producing truly non-contiguous areas, only
closer examination of my code would reveal, I admittedly tested it rather
ahem, late at night.

Excel 2003, running 1 GB RAM, 2.0 gHZ processor in Win XP Pro:

500 increment 00:00:01 total time = 00:00:01
1000 increment 00:00:03 total time = 00:00:04
1500 increment 00:00:08 total time = 00:00:12
2000 increment 00:00:16 total time = 00:00:28
2500 increment 00:00:25 total time = 00:00:53
3000 increment 00:00:39 total time = 00:01:32
3500 increment 00:00:56 total time = 00:02:28
4000 increment 00:01:13 total time = 00:03:41
4500 increment 00:01:31 total time = 00:05:12
5000 increment 00:01:55 total time = 00:07:07
5500 increment 00:02:49 total time = 00:09:56
6000 increment 00:03:08 total time = 00:13:04
6500 increment 00:03:38 total time = 00:16:42
7000 increment 00:04:38 total time = 00:21:20
7500 increment 00:05:10 total time = 00:26:30
8000 increment 00:05:58 total time = 00:32:28
8500 increment 00:06:37 total time = 00:39:05
9000 increment 00:07:36 total time = 00:46:41
9500 increment 00:08:28 total time = 00:55:09
10000 increment 00:09:19 total time = 01:04:28
10500 increment 00:10:25 total time = 01:14:53
11000 increment 00:11:42 total time = 01:26:35
11500 increment 00:12:37 total time = 01:39:12
12000 increment 00:13:42 total time = 01:52:54
12500 increment 00:14:31 total time = 02:07:25
13000 increment 00:16:06 total time = 02:23:31
13500 increment 00:17:06 total time = 02:40:37
14000 increment 00:18:31 total time = 02:59:08
14500 increment 00:19:50 total time = 03:18:58
15000 increment 00:20:57 total time = 03:39:55
15500 increment 00:22:40 total time = 04:02:35
16000 increment 00:24:07 total time = 04:26:42
16500 increment 00:25:19 total time = 04:52:01
17000 increment 00:26:48 total time = 05:18:49
17500 increment 00:28:41 total time = 05:47:30
18000 increment 00:30:15 total time = 06:17:45
18500 increment 00:31:51 total time = 06:49:36


Again, the code:

Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range
Dim Start As Date, LastTime As Date

On Error GoTo Err_
Set AllRange = Cells(2, 1)
Start = Now()
LastTime = Now()
For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
If AllRange.Count Mod 500 = 0 Then
Debug.Print AllRange.Count & _
" increment " & _
Format(Now() - LastTime, "HH:MM:SS") & _
" total time = " & _
Format(Now() - Start, "HH:MM:SS")
LastTime = Now()
DoEvents
End If
End If
Next i
Next j

Exit_Sub:

Exit Sub
Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub

"Peter T" <peter_t@discussions wrote in message
...
It's a small part of a project in development. Unfortunately for the time
being the union bit is under wraps in VB6.

A rough idea of union timings with single cell discontiguous areas,
slightly
longer if all multicells.

areas sec's
500 0.070
1000 0.187
2000 0.535
4000 1.805
8000 6.550

Regards,
Peter T


"keepITcool" wrote in message
.com...

I wouldnt know. But somehow I doubt it.
since vba is non compiled there's a licensing problem too.

I AM interested to see the code ..
which goes back to my struggle
for an efficient 'OUTERSECT' routine.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :

Hi KeepITcool,

at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas..

Just curiosity, do you see any market potential for a routine
(ActiveX) that might substantially overcome this, for say up to 8000
areas.

Regards,
Peter T

pmbthornton at gmail com