Range holding non-contiguous cells

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> 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
news:ezhq%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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
> news:ezhq%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >>
> >> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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
> news:ezhq%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> news:[email protected]...
>> > 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
>> > news:ezhq%[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>>
>> >>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
> > 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
> > news:ezhq%[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>
> >>> 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
news:%23Vfg%[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> 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
>> news:ezhq%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >>
>> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> 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
>> news:ezhq%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>
>>>> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> news:[email protected]...
>> > 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
>> > news:ezhq%[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>>
>> >>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%[email protected]...
> 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
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>>> news:[email protected]...
>>> > 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
>>> > news:ezhq%[email protected]...
>>> >> 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" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >>>
>>> >>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:[email protected]...
> >> 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
> >> news:ezhq%[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>>
> >>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:%[email protected]...
>> 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
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>>>> news:[email protected]...
>>>> > 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
>>>> > news:ezhq%[email protected]...
>>>> >> 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" <[email protected]> wrote in message
>>>> >> news:[email protected]...
>>>> >>>
>>>> >>> 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" <[email protected]> wrote in message
news:[email protected]...
> Which statements appear to be untrue?
>
> Note that the article restricts itself to selection methods involving
> special cells.
>
> --
> Regards,
> Tom Ogilvy
>
> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> > news:[email protected]...
>> >> 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
>> >> news:ezhq%[email protected]...
>> >>> 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" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>>>
>> >>>> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:%[email protected]...
> >> 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
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> >>>> news:[email protected]...
> >>>> > 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
> >>>> > news:ezhq%[email protected]...
> >>>> >> 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" <[email protected]> wrote in message
> >>>> >> news:[email protected]...
> >>>> >>>
> >>>> >>> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> > news:%[email protected]...
>> >> 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
>> >> news:[email protected]...
>> >>> 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" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> >>>> news:[email protected]...
>> >>>> > 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
>> >>>> > news:ezhq%[email protected]...
>> >>>> >> 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" <[email protected]> wrote in message
>> >>>> >> news:[email protected]...
>> >>>> >>>
>> >>>> >>> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
> > 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
news:[email protected]...
> Hi Bill,
>
> I misinterpreted, apologies. But I didn't have the slightest intention to
> "box you in" !
>
> Cheers,
> Peter T
>
>
> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> 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
>> news:[email protected]...
>> > 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