SpecialCells in Excel VBA is very usefull but there is a
bug in Excel 2007, and earlier versions.
XlCellType options are:
The only problem is that there is a limit of 8192 areas that it can handle.
Note: This problem is fixed in Excel 2010
Noncontiguous cells that can be selected in Excel 2010 : 2,147,483,648 cells
Note: All xlCellType options have this problem
Example: Manual select all Blanks in a column to delete the rows
1) Select the column
3) Select Blanks
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
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.