Microsoft KB Archive/832293

= The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel =

Article ID: 832293

Article Last Modified on 11/10/2006

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-





SYMPTOMS
When you create a Microsoft Visual Basic for Applications (VBA) macro that selects multiple non-contiguous ranges in a Microsoft Excel workbook that uses a VBA expression that is similar to the following, actions that were only supposed to occur with non-contiguous cells occur to every cell in the original selection on the worksheet: expression.SpecialCells(XlCellType).expression XlCellType can be any one of the following:
 * xlCellTypeAllFormatConditions
 * xlCellTypeAllValidation
 * xlCellTypeBlanks
 * xlCellTypeComments
 * xlCellTypeConstants
 * xlCellTypeFormulas
 * xlCellTypeSameFormatConditions
 * xlCellTypeSameValidation
 * xlCellTypeVisible



CAUSE
This behavior occurs if you select more than 8,192 non-contiguous cells with your macro. Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.

Typically, if you try to manually select more than 8,192 non-contiguous cells, you receive the following error message:

The selection is too large.

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.



WORKAROUND
To work around this behavior, you may want to create a looping structure in your VBA macro that handles less than the maximum 8,192 cells.



STATUS
This behavior is by design.

Additional query words: XL2007 XL XL2003 XL2002 XL2000 XL97 XL2K3 XL2K+3 XL2k2 XL2K+2 XLXP XL2k

Keywords: kbvba kbfunctions kbformat kbmacro kbcodesnippet kbcode kbprb KB832293

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.