Range("A2:A50").SpecialCells(xlCellTypeBlanks) does not include blank
cells below the last non-blank cell in A2:A50. Is this correct
behaviour? If so it seems illogical to me. Or, am I doing something
wrong?
Ken Johnson

Hi Ken,

Range("A2:A50").SpecialCells(xlCellTypeBlanks)

defines the blank cells in Range("A2:A50").

That seems entirely logical to me.

What range do you want to include?

---
Regards,
Norman



"Ken Johnson" wrote in message
ups.com...
Range("A2:A50").SpecialCells(xlCellTypeBlanks) does not include blank
cells below the last non-blank cell in A2:A50. Is this correct
behaviour? If so it seems illogical to me. Or, am I doing something
wrong?
Ken Johnson

Hi Norman,
Say the last non-blank cell in the range A2:A50 is A25, then
Range("A2:A50").SpecialCells(xlCellTypeBlanks) only returns the range
of blank cells from A2 down to A24 and leaves out the range of blank
cells from A26 down to A50. Leaving out those blank cells is, to me,
the illogical part, considering that I have specified the range A2:A50
for the SpecialCells Method to work on.
Ken Johnson

Hi Ken,

Your scenario does not accord with either my expectations or my experience.

On a blank sheet, try running the following sub:

'=============
Public Sub TestIt()
Dim rng As Range, rng2 As Range

Set rng = Range("A2:A50")

rng.Value = "TEST"

Range("A2, A5, A7, A26:A50").ClearContents


Set rng2 = rng.SpecialCells(xlCellTypeBlanks)

Debug.Print rng2.Address

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

For me this returned the following in the immediate window:

$A$2,$A$5,$A$7,$A$26:$A$50

Is your experience different?

Of course, if your cells only *appear* blank - perhaps containing formulas
that return an empty string - then the these will not be included as blank
cells by the SpecialCells method. Again, this is entirely logical.

---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
Hi Norman,
Say the last non-blank cell in the range A2:A50 is A25, then
Range("A2:A50").SpecialCells(xlCellTypeBlanks) only returns the range
of blank cells from A2 down to A24 and leaves out the range of blank
cells from A26 down to A50. Leaving out those blank cells is, to me,
the illogical part, considering that I have specified the range A2:A50
for the SpecialCells Method to work on.
Ken Johnson

Hi Norman,
I ran your test and got a result identical to yours. Also, when I run
my original code (below), after having run your test, it too produces a
result identical to yours.
However, if I run my code on a new sheet that only has one non-blank
cell (A25 = 15) then the SpecialCells Method does not include A26:A50,
which are genuine blank cells. One thing worth noting is that on a new
sheet A26:A50 cells have never been entered, whereas running your test
code first gives them the value "TEST" then clears them, so it looks to
me as though Excel's SpecialCells(xlCellTypeBlanks) does not include
any Blank cells that have never been worked with. If this is the case
then it surely is something that Microsoft should have pointed out in
their help files. Or have I overlooked something?

Public Sub BlanksTest()
Dim rngAllBlanks As Range
Dim WorkArea As Range
Set WorkArea = ActiveSheet.Range("A2:A50")
Set rngAllBlanks = WorkArea.SpecialCells(xlCellTypeBlanks)
Debug.Print rngAllBlanks.Address
End Sub

Ken Johnson

Hi Norman,
Perhaps I should have said "Excel's SpecialCells(xlCellTypeBlanks) does
not include any Blank cells that have never been worked with and are
below the bottom-most non-blank cell in the specified range".
Ken Johnson

Hi Ken,

Again in a blank sheet, try:

'==============
Public Sub TestIt2()
Dim rng As Range, rng2 As Range

Set rng = Range("A2:A50")

Range("A25, A100") = 15

Set rng2 = rng.SpecialCells(xlCellTypeBlanks)

Debug.Print rng2.Address

End Sub

<<==============

For me this returns: $A$2:$A$24,$A$26:$A$50

You will note that I have populated cell A100. This is because the
SpecialCells method applies an implicit intersect with the used range. In
consequence, if the above sub were to populate only cell A25, the reported
blank cell range would be $A$2:$A$24.

Effectively, in this situation, Excel interprets:

Range("A2:A50").SpecialCells (xlCellTypeBlanks)
as:
Intersect(Range("A2:A50").SpecialCells(xlCellTypeB lanks), _
ActiveSheet.UsedRange)

In other words, the SpecialCells Method disregards cells outside the used
range.


---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
Hi Norman,
I ran your test and got a result identical to yours. Also, when I run
my original code (below), after having run your test, it too produces a
result identical to yours.
However, if I run my code on a new sheet that only has one non-blank
cell (A25 = 15) then the SpecialCells Method does not include A26:A50,
which are genuine blank cells. One thing worth noting is that on a new
sheet A26:A50 cells have never been entered, whereas running your test
code first gives them the value "TEST" then clears them, so it looks to
me as though Excel's SpecialCells(xlCellTypeBlanks) does not include
any Blank cells that have never been worked with. If this is the case
then it surely is something that Microsoft should have pointed out in
their help files. Or have I overlooked something?

Public Sub BlanksTest()
Dim rngAllBlanks As Range
Dim WorkArea As Range
Set WorkArea = ActiveSheet.Range("A2:A50")
Set rngAllBlanks = WorkArea.SpecialCells(xlCellTypeBlanks)
Debug.Print rngAllBlanks.Address
End Sub

Ken Johnson

Hi Norman,
Thanks for clearing that up for me, your speedy response is much
appreciated.
Ken Johnson

Hi Ken,

To avoid unpleasant surprises, there are two other characteristics of the
SpecialCells method which may be of interest:

(1) If the SpecialCells method is applied to a single cell range, the range
of interest becomes the entire used range. In this respect, the SpecialCells
method is consistent with the Excel implementation.

(2) If the range passed to the SpecialCells method comprises more than 8192
non-contiguous areas, the entire range will be returned.

---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
Hi Norman,
Thanks for clearing that up for me, your speedy response is much
appreciated.
Ken Johnson

Hi Norman,
Does characteristic (1) mean that A1.SpecialCells(etc) is equivalent to
UsedRange.SpecialCells(etc), which I guess could possibly occur at run
time if A1 is determined then?
Do both characteristics mean that unwanted things could happen to cells
you want left untouched?
If so I will be more careful in future!
Ken Johnson

Hi Ken,

Does characteristic (1) mean that A1.SpecialCells(etc) is equivalent to
UsedRange.SpecialCells(etc)

Yes. In the last scenario (with only cell A25 populated):

?range("A1").SpecialCells(xlCellTypeBlanks).Addres s

returned: $A$1:$A$24

The 8192 non-contiguous areas issue is only something that needs to be
addressed for relatively large ranges. For more information on this see the
MSKB artiicle # 832293:

http://support.microsoft.com/default...b;en-us;832293


---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
Hi Norman,
Does characteristic (1) mean that A1.SpecialCells(etc) is equivalent to
UsedRange.SpecialCells(etc), which I guess could possibly occur at run
time if A1 is determined then?
Do both characteristics mean that unwanted things could happen to cells
you want left untouched?
If so I will be more careful in future!
Ken Johnson

Hi Norman,
Thanks again for all your help.
Ken Johnson