Problem with testing re 8192..

if it's more than 8192 areas then SpecialCells will return 1 solid
range. no error. So you cant test for 8191.

I've done exhaustive testing on this... it's either 8192 or 1.
(regardless of the size/shape of the individual areas.)

Bug is still there in Excel 2003.

although M$ phrases it somewhat differently...

The Excel VBA function ".SpecialCells(xlCellTypeBlanks)" does not work
as expected
<http://support.microsoft.com/default.aspx?scid=kb;en-us;832293
snippet.. just for laughs :)
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.
======================================
STATUS:This behavior is by design.
======================================


If you want to test yourself..

Sub SpecialCellsCantHandleMoreThan8192AreasBugDemo()
Dim r&, c&, n&, rs As Range
Dim v(1 To 2 ^ 16, 1 To 1)
'Fill cells alternating
Cells.Clear
n = ActiveSheet.UsedRange.Count
For r = LBound(v, 1) To UBound(v, 1)
For c = LBound(v, 2) To UBound(v, 2)
If (r + c) Mod 2 = 0 Then v(r, c) = 1
Next
Next
Cells(1, 1).Resize(UBound(v, 1), UBound(v, 2)) = v
'Now let's use SpecialCells to find the blanks
For r = 8192 To 8193

With Cells(1, 1).Resize(2 * r, 1)
With .SpecialCells(xlBlanks)
.Select
If .Areas.Count 1 Then
MsgBox "8192 areas found.. 1 more?"
Else
MsgBox "Oops.. SpecialCells cant handle more than 8192 areas"
& _
vbNewLine & "it will NOT throw an error, but return 1
large area instead" & _
vbNewLine & Application.CountBlank(.Cells) & " blank
cells s/b selected", vbCritical, "BugDemo"
End If
End With
End With
Next
End Sub




You MUST test for 1. Cuz that's the danger point.

Re problems with array.. wouldn't explicit type conversion
in the restore phase be enough?




I'll read all the comments tomorrow.

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


Peter T wrote :

Hi Norman

I have been working with this method to subtract ranges
for some while. I had always attributed this to Dana
DeLouis, but reading the links in this thread it appears
to be a logical development of an old idea of Tom Ogilvy's
(I don't mean to detract anything from Dana's clever idea).

[snap]