Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

SpecialCells limit problem

SpecialCells in Excel VBA is very usefull but there is a bug in Excel 2007, and earlier versions.

XlCellType options are:
• xlCellTypeAllFormatConditions
• xlCellTypeAllValidation
• xlCellTypeBlanks
• xlCellTypeComments
• xlCellTypeConstants
• xlCellTypeFormulas
• xlCellTypeSameFormatConditions
• xlCellTypeSameValidation
• xlCellTypeVisible

The only problem is that there is a limit of 8192 areas that it can handle.;en-us;832293

Note: This problem is fixed in Excel 2010
Noncontiguous cells that can be selected in Excel 2010 : 2,147,483,648 cells


Example for xlCellTypeBlanks

Note: All xlCellType options have this problem

Example: Manual select all Blanks in a column to delete the rows

1) Select the column
2) F5>Special
3) Select Blanks
4) OK

If there are more then 8192 areas you will see this MsgBox

But if we do the same with VBA, It works great if there are no more then 8192 areas. However, if there are more than 8192 areas, then all the data on your sheet will be deleted without any warning. ( And that's not funny!)

If we use this macro below and there are more then 8192 areas we have a problem.

Sub DeleteBlankRows_1()
'This macro delete all rows with a blank cell in column A
    On Error Resume Next  'In case there are no blank cells
    On Error GoTo 0
End Sub

With the code below, we can test if we have more the 8192 areas so we won't have the problem
that it delete all date on the worksheet.

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
    Dim CCount As Long
    On Error Resume Next
    With Columns("A")  ' You can also use a range like this Range("A1:A8000")
        CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count
        If CCount = 0 Then
            MsgBox "There are no blank cells"
        ElseIf CCount = .Cells.Count Then
            MsgBox "There are more then 8192 areas"
        End If
    End With
    On Error GoTo 0
End Sub



To work around this behavior, you may want to create a looping structure in your VBA macro that handles blocks of 16384 cells (not possible to have more then 8192 different areas in 16384 cells).

Looping example from Debra Dalgleish

See also this page from David McRitchie

Another way is to sort you data column before you apply the filter.