strange result from SpecialCells

[seron]

Why do I get the following strange result? Cell E11 contains the single letter 'f'. In the imediate window:

? activesheet.range("E11").SpecialCells(xlCellTypeConstants).address
$K$2:$N$2,$D$4,$H$4,$L$4,$R$4,$K$4:$K$5,$G$6:$Z$6, ... (the list is pretty long)

while the following produces the expected result, E11.

? activesheet.range("E11:E12").SpecialCells(xlCellTypeConstants).address
$E$11

seron

[davesexcel]

Why do I get the following strange result? Cell E11 contains the single letter 'f'. In the imediate window:

? activesheet.range("E11").SpecialCells(xlCellTypeConstants).address
$K$2:$N$2,$D$4,$H$4,$L$4,$R$4,$K$4:$K$5,$G$6:$Z$6, ... (the list is pretty long)

while the following produces the expected result, E11.

? activesheet.range("E11:E12").SpecialCells(xlCellTypeConstants).address
$E$11

seron

Watcha trying to do

[seron]

Watcha trying to do

Hi Dave.

Within a userselected range of cell I'd like to find which cells contain constant values, e.g. F, AB, N etc. The user may select one or multiple cells and they may be disjoint.

The problem is that if the selected range is only one cell then SpecialCells(xlCellTypeConstants) produces unpredictable and seemingly random sets of cells that may be outside of the selected range. I wonder why it does that.

[seron]

bump

Is there anyone who could confirm this behaviour?

[davesexcel]

kind of hard to confirm anything without the code , or what you are trying to do

[Richard Schollar]

Seron

It does appear that SpecialCells returns all the cells matching the constraint on a given worksheet if it is prefixed by a single cell object. So, to get areound that in code, you probably want to ascertain the size of your given range object first eg:

Please Login or Register  to view this content.

and put in place code to test the contents directly (eg HasFormula property or .Value="") in the case of it being a single cell.

Richard