SpecialCells limit problem

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

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

Workeround

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://dmcritchie.mvps.org/excel/delempty.htm

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

Ron de BruinExcel Automation