Using SpecialCells in VBA is very useful for a lot of things in Excel.

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.
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

Note: This 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
    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    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"
        Else
            .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        End If

    End With
 
    On Error GoTo 0
End Sub

WORKAROUND

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
http://www.contextures.com/xlDataEntry02.html#macro03

See also this page from David McRitchie
http://www.mvps.org/dmcritchie/excel/delempty.htm#failure


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