Hi Bill,
I have been able to add substantially more than 8000 areas ... unless you
doubt the integrity of my code: Remember this is using Cells.Count so it
is definitely accurate.
If Cells.Count is a definitive proof, how would you explain the result of:
Sub TesterX()
Dim rng As Range, rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A16386")
rng.ClearContents
For i = 1 To 16385 Step 2
Cells(i, "A") = "=NA()"
Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)
Application.ScreenUpdating = True
MsgBox rng1.Cells.Count
End Sub
I note Peter's comment in his adjacent post:
'==================================
The article Norman referred you to relates to the
maximum number of areas that can be returned using SpecialCells, not the
maximum number that can be unioned in a loop.
'<<==================================
I believe, that the SpecialCells method is a special case and that the first
quoted line of that article:
'==========================================
Excel only supports a maximum of 8,192 non-contiguous cells through VBA
macros
'==========================================
should be interpreted literally.
Of course, if you are able to adapt your macro to select or demonstrably
manipulate a 8192+ non-contiguous area range, then my understanding will be
demonstrated to be incorrect and I will be genuinely delighted to revise my
view.
---
Regards,
Norman
"William Benson" wrote in message
...
Sorry to post this twice, but I should have replied to the deepest message
in the thread:
I have been able to add substantially more than 8000 areas ... unless you
doubt the integrity of my code: Remember this is using Cells.Count so it
is definitely accurate. Whether I am producing truly non-contiguous areas,
only closer examination of my code would reveal, I admittedly tested it
rather ahem, late at night.
Excel 2003, running 1 GB RAM, 2.0 gHZ processor in Win XP Pro:
500 increment 00:00:01 total time = 00:00:01
1000 increment 00:00:03 total time = 00:00:04
1500 increment 00:00:08 total time = 00:00:12
2000 increment 00:00:16 total time = 00:00:28
2500 increment 00:00:25 total time = 00:00:53
3000 increment 00:00:39 total time = 00:01:32
3500 increment 00:00:56 total time = 00:02:28
4000 increment 00:01:13 total time = 00:03:41
4500 increment 00:01:31 total time = 00:05:12
5000 increment 00:01:55 total time = 00:07:07
5500 increment 00:02:49 total time = 00:09:56
6000 increment 00:03:08 total time = 00:13:04
6500 increment 00:03:38 total time = 00:16:42
7000 increment 00:04:38 total time = 00:21:20
7500 increment 00:05:10 total time = 00:26:30
8000 increment 00:05:58 total time = 00:32:28
8500 increment 00:06:37 total time = 00:39:05
9000 increment 00:07:36 total time = 00:46:41
9500 increment 00:08:28 total time = 00:55:09
10000 increment 00:09:19 total time = 01:04:28
10500 increment 00:10:25 total time = 01:14:53
11000 increment 00:11:42 total time = 01:26:35
11500 increment 00:12:37 total time = 01:39:12
12000 increment 00:13:42 total time = 01:52:54
12500 increment 00:14:31 total time = 02:07:25
13000 increment 00:16:06 total time = 02:23:31
13500 increment 00:17:06 total time = 02:40:37
14000 increment 00:18:31 total time = 02:59:08
14500 increment 00:19:50 total time = 03:18:58
15000 increment 00:20:57 total time = 03:39:55
15500 increment 00:22:40 total time = 04:02:35
16000 increment 00:24:07 total time = 04:26:42
16500 increment 00:25:19 total time = 04:52:01
17000 increment 00:26:48 total time = 05:18:49
17500 increment 00:28:41 total time = 05:47:30
18000 increment 00:30:15 total time = 06:17:45
18500 increment 00:31:51 total time = 06:49:36
Again, the code:
Sub fghijx()
Dim aRange(1 To 65536, 1 To 256) As Range, i As Long, j As Long
Dim AllRange As Range
Dim Start As Date, LastTime As Date
On Error GoTo Err_
Set AllRange = Cells(2, 1)
Start = Now()
LastTime = Now()
For j = 1 To 256
For i = 1 To 65536
If (j Mod 2) < (i Mod 2) Then
Set aRange(i, j) = Cells(i, j)
Set AllRange = Union(AllRange, aRange(i, j))
If AllRange.Count Mod 500 = 0 Then
Debug.Print AllRange.Count & _
" increment " & _
Format(Now() - LastTime, "HH:MM:SS") & _
" total time = " & _
Format(Now() - Start, "HH:MM:SS")
LastTime = Now()
DoEvents
End If
End If
Next i
Next j
Exit_Sub:
Exit Sub
Err_:
MsgBox "col " & j & " row " & i & " Err # " & Chr(13) & _
Err.Number & " " & Err.Description
Resume Exit_Sub
End Sub
"Peter T" <peter_t@discussions wrote in message
...
It's a small part of a project in development. Unfortunately for the time
being the union bit is under wraps in VB6.
A rough idea of union timings with single cell discontiguous areas,
slightly
longer if all multicells.
areas sec's
500 0.070
1000 0.187
2000 0.535
4000 1.805
8000 6.550
Regards,
Peter T
"keepITcool" wrote in message
.com...
I wouldnt know. But somehow I doubt it.
since vba is non compiled there's a licensing problem too.
I AM interested to see the code ..
which goes back to my struggle
for an efficient 'OUTERSECT' routine.
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
Peter T wrote :
Hi KeepITcool,
at around 500 areas union start to slow down.
and will come to a virtual standstill at around
1500 areas..
Just curiosity, do you see any market potential for a routine
(ActiveX) that might substantially overcome this, for say up to 8000
areas.
Regards,
Peter T
pmbthornton at gmail com