.SpecialCells(xlCellTypeConstants) method quirky behavior in Exel 2010

[VBA Grunt]

I had a behavioral problem with .SpecialCells(xlCellTypeConstants) method that just erased all of the data in one of my spreadsheets today.

I have been using the code for months with no undesired behavior until today. I have done a lot of debugging on this and managed to strip down the errant code to a very simple example that generates the problem behavior every time in a freshly created spreadsheet.

	A	B	C
1	text
2		text
3
4			text
5

using a spreadsheet like the simulated one shown above

Please Login or Register  to view this content.

will print "$A$1,$B$2,$C$4" in the immediate window
while

Please Login or Register  to view this content.

will print "$A$1" as expected and

Please Login or Register  to view this content.

will generate "Run-time error '1004': Application-defined or object-defined error" as expected.

It appears that every time an object refers to a single cell and the special cells method is applied to it, the new object returns all constants on the spreadsheet.

Has anyone run into this problem before and figured out a work around for it?

Since I was referencing a single cell today (for the first time) with the special cells method and the line of code just happened to end with .ClearContents the code very efficiently wiped out all of my constant cells in the spreadsheet.

Any insights or ideas will be greatly appreciated,

Thanks,

[JosephP]

specialcells has always had that behavior. a simple test of the range's Count property first should suffice-you don't really need specialcells for one cell ;-)

[VBA Grunt]

Thank you for the reply Joseph. For months my code has worked just fine because the base range always equated to more than one cell. However this morning the base range happened to be a single cell and data carnage ensued.

I'm sorry to hear that this has been a bug or undesirable feature in Excel all along that has never been fixed.

I'll use the count property to check for a single cell and then conditionally clear the contents of the cell if it does not contain a formula.

I'm sad to say that over the years I've had to program quite a few work arounds in VBA due to Excel not referencing things like I would expect when using various methods.

Thanks again for the insight.

[JosephP]

me too. in case you didn't know already using specialcells(xlcelltypelastcell) will ignore any range you specify and return the last cell of the usedrange