Hi KL,

My interest was not a matter of semantics but uniquely the question of
possible (mis)interpretation: the mere fact that cell and area may be
interpreted differently might have some bearing.

Before leaving this matter, I note that I have omitted to respond to your
challenge:

Your code returns exactly 8192 areas, so try this one (and if you still
believe that it is independent of the # of blank cells, try reproducing
the problem with <=8192 blank cells):

Let me, therefore, rectify: try this version of the previously suggested
code:

'=============
Public Sub Demo2()
Dim i As Long
Dim rng As Range, rng1 As Range, rng2 As Range

Application.ScreenUpdating = False

Set rng = Range("A1:A16384")

rng.Value = "XYZ"

For i = 1 To rng.Rows.Count Step 2
rng.Rows(i).Clear
Next i

Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
Set rng2 = rng.Columns(1).SpecialCells(xlCellTypeBlanks)

MsgBox "Rng1 Areas =" & rng1.Areas.Count _
& vbNewLine & _
"Rng2 Areas =" & rng2.Areas.Count

Application.ScreenUpdating = True

End Sub
'<<=============

---
Regards,
Norman


"KL" wrote in message
...
OK let's attribute this to my poor command of English, but just for
clarity "This method has a limitation of max 8,192 non-contiguous cells"
is not mine but rather Microsoft's :-) English isn't my native language,
so I am sure you can explain to me how can "8,192 non-contiguous cells"
mean anything different to "8,192 areas" in Excel.

Best regards and Merry X-Mas
KL


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

I suspect that you misunderstand both my statement and the purpose of my
post, which had no polemical intent,.

I am well aware of the distinction between areas and cells and the
relevance of these to the use of the SpecialCells method. As I indicated,
I have referred to this precise issue in numerous previous posts,

My response, which was primarily aimed at the OP, was intended to
indicate that the number of blank cells was not a limiting issue.

I was concerned that your use of the expression:

This method has a limitation of max 8,192 non-contiguous cells

without any reference to areas or ranges might be interpreted as
indicating a limit to the number of blank cells.

This concern was not alleviated by your subsequent statement:

For this to become an issue in this specific example one needs to have
at least 16385 rows where every other row is blank (so the risk might
be remote).

which, as it implicitly refers to 8192 blank cells, might, I felt, serve
to reinforce any misconception.

I therefore provided an example of a viable use of the SpecialCells
method in which the range of interest comprised over 16k blank cells.
Obviously, the example could readily be adapted to produce analogous
results with much higher numbers of blank cells. The question of the
number of areas was not an issue: indeed the only reference to areas and,
implicitly, their relevance, was in my post. It was, as I have indicated,
this contextual omission which prompted my response.


---
Regards,
Norman


"KL" wrote in message
...
Hi Norman,

It should be noted, however, that the constraining factor is
independent of the number of blank cells. To demonstrate this try:

Oh yes it is, but not only of the number of blank cells but the way they
are distributed. As I said in my previous message "for this to become an
issue in this specific example one needs to have at least 16385 rows
where every other row is blank..." In other words there need to be more
than 8192 areas. Your code returns exactly 8192 areas, so try this one
(and if you still believe that it is independent of the # of blank
cells, try reproducing the problem with <=8192 blank cells):

Public Sub Demo()
Dim i As Long
Dim rng As Range, rng1 As Range

Application.ScreenUpdating = False
Set rng = Range("A1").Resize(8192 * 3 - 1)
rng.Value = "XYZ"
For i = 1 To rng.Count Step 3
Cells(i, 1).Resize(2).Clear
Next i
Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
MsgBox rng1.Areas.Count
Application.ScreenUpdating = True
Exit Sub
End Sub

Regards,
KL