Range holding non-contiguous cells

[William Benson]

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

 

[Norman Jones]

Hi Bill,

According to MSKB # 832293

http://support.microsoft.com/default.aspx?scid=kb;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.

 

[keepITcool]

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 :

 

[Peter T]

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

 

[keepITcool]

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 :

 

[Peter T]

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

 

[William Benson]

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

 

[William Benson]

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]

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

 

[Norman Jones]

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.

 

[William Benson]

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]

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

 

[William Benson]

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

 

[William Benson]

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]

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.

 

[Norman Jones]

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.

 

[Tom Ogilvy]

Which statements appear to be untrue?

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

 

[William Benson]

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.

 

[William Benson]

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.

 

[Peter T]

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