Overcoming Limitations in the VBA SpecialCells Function

[Rick Rothstein]

VBA's SpecialCells function is quite powerful (if you are unfamiliar with it, you should look it up in the help files)... it is the code equivalent of the "Special..." button on the Go To dialog box (press CNTRL+G to see it). However, in versions of Excel prior to 2010, it has an annoying limitation... it cannot process more than 8192 non-contiguous areas (an area being a contiguous range cells, no matter how many cells there are in that range). And if that limit is exceeded, no warning is issued... the function simply ignores the SpecialCells function as if it were not there meaning the code line ends up referencing the original range of cells in their entirety which is guaranteed to produce result you will not want. The only way I have found to get around this limitation is to grab chunks of cells that are guaranteed not to contain more than 8192 areas within it. I'll use a single column of cells for example purposes because the set up is easy and that is what the vast majority of people use when employing the SpecialCells function (this concept can be extended to a two-dimensional group of areas, but the setup starts to get a little messy). So, looking down a single column of cells, I set up a loop that process no more than 16000 total cells for each iteration since that number can, at most, contain only 8000 non-contiguous areas (in this case, single cells). Since a lot of operations (think deleting entire rows) require the loop to iterate from bottom up (to avoid blanks outside the 16000-cell chunk from being moved into the area that has just been processed and,hence, subsequently being missed during the next iteration of the loop because they get "skipped" over). Having to work from the bottom up causes a problem if there is not an even muliple of 16000 data rows (you cannot just start from the last data cell because working backwards through the chucks will cause reference problems for the beginning cells). I solve this problem by including the empty cells past the last data cell down to the cell that is an exact multiple of my 16000 row chuck size... this calculation is included within the For statement for the For..Next loop. The downside of doing this is all those blank rows after the last data item will unnecessarily be deleted... no real harm there unless there is data in other columns below the last data item in the column being processed. Okay, that is enough "theory", here is example code (modify it as needed for you own setups)... it deletes entire rows where Column D contains blanks (real blanks, not empty string like "" that result from formulas) and works for any number of data cells (although, obviously, if you know you will never have more than 16000 rows of data, you can dispense with the loop and use the SpecialCells function directly in a single code line).

Sub RemoveRowWhereBlankInColumnD()
Dim X As Long, LastRow As Long
Const ChunkSize As Long = 16000
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
On Error Resume Next
For X = 1 + ChunkSize * Int(LastRow / ChunkSize) To 1 Step -ChunkSize
Cells(X, "D").Resize(ChunkSize).SpecialCells(xlBlanks).EntireR ow.Delete
Next
On Error GoTo 0
End Sub