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

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

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

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

Subject to resources and patience you may well be able to union 8 million
discontiguous cells. The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.

Norman also demonstrated in this NG that SpecialCells could also fail with
8191 areas, undocumented by MS.

I haven't tied your code, it looks fine but instead of only cell count also
return:
AllRange.Areas.Count

Although I have what I consider a fast union method* I find it's counter
productive to work with more than 8000 areas, and that as a maximum only to
provide a user selection. For other purposes better to break doen into
smaller groups.

Can I ask for what purpose do you want to union so many areas.

Regards,
Peter T

* 6 seconds in my old P2 350 vs 6 minutes in your modern machine


"William Benson" wrote in message
...
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

Hi Bill,

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.

If Cells.Count is a definitive proof, how would you explain the result of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will be
demonstrated to be incorrect and I will be genuinely delighted to revise my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

uh oh, I was afraid you were going to ask ... I was just impressed with
Union's ability to consolidate area addresses, and wondered at that point,
what if the areas' addresses vould not be consolidated because the cells
were non-contiguous. I am sorry to admit I was just trying to see Excel's
limitations.

I did, however, find a half-serious use use and that is because someone made
the point it is more efficient to collect all cells into a massive range and
perform an operation on the entire range, rather than do so step by step for
each cell. Seeing how long it takes Excel to add just several thousand cells
to an area, when they are non-contiguous at least, I think there is a
dichotomy at play... and therefore it may NOT be more efficient to do what
was suggested.

Thanks for asking, and for contributing to my knowledge on this topic, for
sure!
"Peter T" <peter_t@discussions wrote in message
...
Subject to resources and patience you may well be able to union 8 million
discontiguous cells. The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.

Norman also demonstrated in this NG that SpecialCells could also fail with
8191 areas, undocumented by MS.

I haven't tied your code, it looks fine but instead of only cell count
also
return:
AllRange.Areas.Count

Although I have what I consider a fast union method* I find it's counter
productive to work with more than 8000 areas, and that as a maximum only
to
provide a user selection. For other purposes better to break doen into
smaller groups.

Can I ask for what purpose do you want to union so many areas.

Regards,
Peter T

* 6 seconds in my old P2 350 vs 6 minutes in your modern machine


"William Benson" wrote in message
...
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

Hi Norman,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count. The big multi area was useable to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume could
also do similar.

I think the reason your example fails is for the reason we all know (I think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the result of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be
demonstrated to be incorrect and I will be genuinely delighted to revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Thanks for the continued interest in this, it is a fun one for me. I
interrupted my macro and tested in debug mode:
?AllRange.Cells.Count 10,744. Then I added a line
AllRange.Value = "X"
moved the cursor to it and hit F8. The instantaneous result was the letter
"X" in rows column A2, A4, A6, ... , A21488.

i.e., 10,744 non-contiguous cells.

I have not yet tested Norman's code to see how it compared or presented a
counter-example, but I will look at it for sure.

I interrupted it
"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count. The big multi area was useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume
could
also do similar.

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be
demonstrated to be incorrect and I will be genuinely delighted to revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

OK, I ran Norman's code and take his point relative to SpecialCells ... I
also ready the KB article and it sure makes my own results seem paradoxical.
With my admittedly poor background in Excel's design all I can say is that
the statements in that article do not seem to be categorically true.


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

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.

If Cells.Count is a definitive proof, how would you explain the result of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be demonstrated to be incorrect and I will be genuinely delighted to
revise my view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Hi Peter,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count.

I agree, so can I .
Clearly my initial premise was wrong!

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Again agreed. However, the example was given uniquely to question the use of
Count as proof positive.

I would assume that there *must* be a limit, but in view of the questionable
utility, I am afraid that, in this case, innate sloth will vanquish the
desire to ascertain what that might be.

---
Regards,
Norman



"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count. The big multi area was useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume
could
also do similar.

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be
demonstrated to be incorrect and I will be genuinely delighted to revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Hi Bill,

I can confirm Peter's findings and retract my assertion that 8192
non-contiguous areas represents a universal limit. The limit still applies,
of course, to the SpecialCells method.

I said that I would be delighted to be proved wrong and indeed I am: it is
far better, to be proved wrong and discover that functionality is greater
than anticipated than the reverse.

Thank you for demonstrating my misconception.

I hope, however, that you will not regard it as a churlish qualification if
I query the utility of unions with such astronomically large numbers of
non-contiguous areas.
In the case of SpecialCells, I can see the potential utility; elsewhere I am
not necessarily pursuaded.

Thanks again Bill, I found the question interesting and I know a little more
than I did.

---
Regards,
Norman



"William Benson" wrote in message
...
Thanks for the continued interest in this, it is a fun one for me. I
interrupted my macro and tested in debug mode:
?AllRange.Cells.Count 10,744. Then I added a line
AllRange.Value = "X"
moved the cursor to it and hit F8. The instantaneous result was the letter
"X" in rows column A2, A4, A6, ... , A21488.

i.e., 10,744 non-contiguous cells.

I have not yet tested Norman's code to see how it compared or presented a
counter-example, but I will look at it for sure.

I interrupted it
"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count. The big multi area was useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume
could
also do similar.

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be
demonstrated to be incorrect and I will be genuinely delighted to revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Which statements appear to be untrue?

Note that the article restricts itself to selection methods involving
special cells.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
OK, I ran Norman's code and take his point relative to SpecialCells ... I
also ready the KB article and it sure makes my own results seem
paradoxical.
With my admittedly poor background in Excel's design all I can say is that
the statements in that article do not seem to be categorically true.


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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will
be demonstrated to be incorrect and I will be genuinely delighted to
revise my view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Not churlish, I think it just shows the limits of your imagination ... he
he. Or your own constraints on utility. Just because we in our experience
have not yet found much cause for it does not mean there are not some in a
parallel universe who will find abundant uses for this, have been similarly
misinformed, and will silently thank us for proving all to be well.

:-)

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

I can confirm Peter's findings and retract my assertion that 8192
non-contiguous areas represents a universal limit. The limit still
applies, of course, to the SpecialCells method.

I said that I would be delighted to be proved wrong and indeed I am: it is
far better, to be proved wrong and discover that functionality is greater
than anticipated than the reverse.

Thank you for demonstrating my misconception.

I hope, however, that you will not regard it as a churlish qualification
if I query the utility of unions with such astronomically large numbers
of non-contiguous areas.
In the case of SpecialCells, I can see the potential utility; elsewhere I
am not necessarily pursuaded.

Thanks again Bill, I found the question interesting and I know a little
more than I did.

---
Regards,
Norman



"William Benson" wrote in message
...
Thanks for the continued interest in this, it is a fun one for me. I
interrupted my macro and tested in debug mode:
?AllRange.Cells.Count 10,744. Then I added a line
AllRange.Value = "X"
moved the cursor to it and hit F8. The instantaneous result was the
letter "X" in rows column A2, A4, A6, ... , A21488.

i.e., 10,744 non-contiguous cells.

I have not yet tested Norman's code to see how it compared or presented a
counter-example, but I will look at it for sure.

I interrupted it
"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000 and
correctly returned the range areas count. The big multi area was useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume
could
also do similar.

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not
the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or
demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding
will
be
demonstrated to be incorrect and I will be genuinely delighted to
revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

You are right. I read "Excel only supports a maximum of 8,192 non-contiguous
cells through VBA macros" as a general statement.

Thanks for the help.

"Tom Ogilvy" wrote in message
...
Which statements appear to be untrue?

Note that the article restricts itself to selection methods involving
special cells.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
OK, I ran Norman's code and take his point relative to SpecialCells ... I
also ready the KB article and it sure makes my own results seem
paradoxical.
With my admittedly poor background in Excel's design all I can say is
that
the statements in that article do not seem to be categorically true.


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

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.

If Cells.Count is a definitive proof, how would you explain the result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not
the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that the
first quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or
demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding
will
be demonstrated to be incorrect and I will be genuinely delighted to
revise my view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Hi Norman,

but in view of the questionable
utility, I am afraid that, in this case, innate sloth will vanquish the
desire to ascertain what that might be.

Wonderful!
I will re-quote as my own at the earliest the opportunity next presents
itself. Typically several times a day so not long to wait!

Regards,
Peter T

Hi Bill,

Not churlish, I think it just shows the limits of your imagination ... he
he. Or your own constraints on utility. Just because we in our experience
have not yet found much cause for it does not mean there are not some in a
parallel universe who will find abundant uses for this, have been
similarly
misinformed, and will silently thank us for proving all to be well.

:-)

I know this is addressed to Norman but from a third party perspective
slightly perplexing. Though with the smiley I assume entirely light hearted.

Norman conceded your point very graciously, I thought. However he does
question the practical use of your discovery. Whilst I have no pretensions
as to being an expert there are a few things I have spent a very
considerable time looking at, and this is one of them. On this basis I
entirely agree with Norman, indeed I implied similar earlier.

You replied previously you were only testing the limits of Excel. That's
great and always well worth doing. You have discovered for yourself that
building such a large union, whilst theoretically possible, is not viable.
Others in a parallel universe can learn simply that, and use other ways.

Regards,
Peter T


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

I can confirm Peter's findings and retract my assertion that 8192
non-contiguous areas represents a universal limit. The limit still
applies, of course, to the SpecialCells method.

I said that I would be delighted to be proved wrong and indeed I am: it
is
far better, to be proved wrong and discover that functionality is
greater
than anticipated than the reverse.

Thank you for demonstrating my misconception.

I hope, however, that you will not regard it as a churlish qualification
if I query the utility of unions with such astronomically large numbers
of non-contiguous areas.
In the case of SpecialCells, I can see the potential utility; elsewhere
I
am not necessarily pursuaded.

Thanks again Bill, I found the question interesting and I know a little
more than I did.

---
Regards,
Norman



"William Benson" wrote in message
...
Thanks for the continued interest in this, it is a fun one for me. I
interrupted my macro and tested in debug mode:
?AllRange.Cells.Count 10,744. Then I added a line
AllRange.Value = "X"
moved the cursor to it and hit F8. The instantaneous result was the
letter "X" in rows column A2, A4, A6, ... , A21488.

i.e., 10,744 non-contiguous cells.

I have not yet tested Norman's code to see how it compared or presented
a
counter-example, but I will look at it for sure.

I interrupted it
"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000
and
correctly returned the range areas count. The big multi area was
useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I assume
could
also do similar.

I think the reason your example fails is for the reason we all know (I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the
result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not
the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that
the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or
demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding
will
be
demonstrated to be incorrect and I will be genuinely delighted to
revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Don't know what to say to this Peter. I guess I am entirely sorry for the
matter. I did not feel I was out of line and Norman did say he was glad to
find out certain facts more clearly. Since you felt the need to box me on
the ears one more time (I already took your point earlier), I hope it felt
good for one of us.

....Bill

"Peter T" <peter_t@discussions wrote in message
...
Hi Bill,

Not churlish, I think it just shows the limits of your imagination ... he
he. Or your own constraints on utility. Just because we in our experience
have not yet found much cause for it does not mean there are not some in
a
parallel universe who will find abundant uses for this, have been
similarly
misinformed, and will silently thank us for proving all to be well.

:-)

I know this is addressed to Norman but from a third party perspective
slightly perplexing. Though with the smiley I assume entirely light
hearted.

Norman conceded your point very graciously, I thought. However he does
question the practical use of your discovery. Whilst I have no pretensions
as to being an expert there are a few things I have spent a very
considerable time looking at, and this is one of them. On this basis I
entirely agree with Norman, indeed I implied similar earlier.

You replied previously you were only testing the limits of Excel. That's
great and always well worth doing. You have discovered for yourself that
building such a large union, whilst theoretically possible, is not viable.
Others in a parallel universe can learn simply that, and use other ways.

Regards,
Peter T


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

I can confirm Peter's findings and retract my assertion that 8192
non-contiguous areas represents a universal limit. The limit still
applies, of course, to the SpecialCells method.

I said that I would be delighted to be proved wrong and indeed I am: it
is
far better, to be proved wrong and discover that functionality is
greater
than anticipated than the reverse.

Thank you for demonstrating my misconception.

I hope, however, that you will not regard it as a churlish
qualification
if I query the utility of unions with such astronomically large
numbers
of non-contiguous areas.
In the case of SpecialCells, I can see the potential utility; elsewhere
I
am not necessarily pursuaded.

Thanks again Bill, I found the question interesting and I know a little
more than I did.

---
Regards,
Norman



"William Benson" wrote in message
...
Thanks for the continued interest in this, it is a fun one for me. I
interrupted my macro and tested in debug mode:
?AllRange.Cells.Count 10,744. Then I added a line
AllRange.Value = "X"
moved the cursor to it and hit F8. The instantaneous result was the
letter "X" in rows column A2, A4, A6, ... , A21488.

i.e., 10,744 non-contiguous cells.

I have not yet tested Norman's code to see how it compared or
presented
a
counter-example, but I will look at it for sure.

I interrupted it
"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

I can also union significantly more than 8000 areas, just did 16000
and
correctly returned the range areas count. The big multi area was
useable
to
do say
rng.value = 1
which gave me a chequerboard of 16000 1's

I'm not patient enough to try the OP's code but looking at it I
assume
could
also do similar.

I think the reason your example fails is for the reason we all know
(I
think
you better than anyone <g) due to the SpecialCells limit.

Regards,
Peter T

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

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.

If Cells.Count is a definitive proof, how would you explain the
result
of:

Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents

For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)

Application.ScreenUpdating = True
MsgBox rng1.Cells.Count

End Sub

I note Peter's comment in his adjacent post:

'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not
the
maximum number that can be unioned in a loop.
'<<==================================

I believe, that the SpecialCells method is a special case and that
the
first
quoted line of that article:

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

should be interpreted literally.

Of course, if you are able to adapt your macro to select or
demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding
will
be
demonstrated to be incorrect and I will be genuinely delighted to
revise
my
view.


---
Regards,
Norman



"William Benson" wrote in message
...
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

Hi Bill,

I misinterpreted, apologies. But I didn't have the slightest intention to
"box you in" !

Cheers,
Peter T


"William Benson" wrote in message
...
Don't know what to say to this Peter. I guess I am entirely sorry for the
matter. I did not feel I was out of line and Norman did say he was glad to
find out certain facts more clearly. Since you felt the need to box me on
the ears one more time (I already took your point earlier), I hope it felt
good for one of us.

...Bill

"Peter T" <peter_t@discussions wrote in message
...
Hi Bill,

Not churlish, I think it just shows the limits of your imagination ...
he
he. Or your own constraints on utility. Just because we in our
experience
have not yet found much cause for it does not mean there are not some
in
a
parallel universe who will find abundant uses for this, have been
similarly
misinformed, and will silently thank us for proving all to be well.

:-)

I know this is addressed to Norman but from a third party perspective
slightly perplexing. Though with the smiley I assume entirely light
hearted.

Norman conceded your point very graciously, I thought. However he does
question the practical use of your discovery. Whilst I have no
pretensions
as to being an expert there are a few things I have spent a very
considerable time looking at, and this is one of them. On this basis I
entirely agree with Norman, indeed I implied similar earlier.

You replied previously you were only testing the limits of Excel. That's
great and always well worth doing. You have discovered for yourself that
building such a large union, whilst theoretically possible, is not
viable.
Others in a parallel universe can learn simply that, and use other ways.

Regards,
Peter T

< snip

No problem Peter, as I wrote to you privately (and I hope it was not out of
line to do so) I did not ask the original question lightly, though I made it
seem so in some of the replies I gave to you and other of my betters here,
sensing their doubt about my objectives.

Bill

"Peter T" <peter_t@discussions wrote in message
...
Hi Bill,

I misinterpreted, apologies. But I didn't have the slightest intention to
"box you in" !

Cheers,
Peter T


"William Benson" wrote in message
...
Don't know what to say to this Peter. I guess I am entirely sorry for the
matter. I did not feel I was out of line and Norman did say he was glad
to
find out certain facts more clearly. Since you felt the need to box me on
the ears one more time (I already took your point earlier), I hope it
felt
good for one of us.

...Bill

"Peter T" <peter_t@discussions wrote in message
...
Hi Bill,

Not churlish, I think it just shows the limits of your imagination ...
he
he. Or your own constraints on utility. Just because we in our
experience
have not yet found much cause for it does not mean there are not some
in
a
parallel universe who will find abundant uses for this, have been
similarly
misinformed, and will silently thank us for proving all to be well.

:-)

I know this is addressed to Norman but from a third party perspective
slightly perplexing. Though with the smiley I assume entirely light
hearted.

Norman conceded your point very graciously, I thought. However he does
question the practical use of your discovery. Whilst I have no
pretensions
as to being an expert there are a few things I have spent a very
considerable time looking at, and this is one of them. On this basis I
entirely agree with Norman, indeed I implied similar earlier.

You replied previously you were only testing the limits of Excel.
That's
great and always well worth doing. You have discovered for yourself
that
building such a large union, whilst theoretically possible, is not
viable.
Others in a parallel universe can learn simply that, and use other
ways.

Regards,
Peter T

< snip

I wrote to you privately

I got the off-line message and have replied

Peter T