2006.03.16 08:25 PM
Determine if an Excel Range Is Empty in VBA
One obvious candidate for determining whether all of the cells of a Range are empty is the COUNTBLANK function, which is accessed in VBA using the global Excel object's WorksheetFunction class. You can wrap it in a function like this:
Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean RangeIsEmpty = (WorksheetFunction.CountBlank(SourceRange) = SourceRange.Count) End Function
Pretty simple. However, COUNTBLANK includes cells having formulas that return empty strings (e.g., =""), which may not be what you want. If what you really want is empty empty, as in no formulas, constants, errors - nothing, then a better choice is probably the COUNTA function:
Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean RangeIsEmpty = (WorksheetFunction.CountA(SourceRange) = 0) End Function
Alternatively, you might be tempted to use the SpecialCells method of the Range object, passing it xlCellTypeBlanks for the Type parameter. If you do, though, you'll have to take care to deal with some of SpecialCells' special behaviors (at least in Excel 2003, I haven't looked back at 2002/XP or 2000 to test any of this).
The most important thing to know about SpecialCells is that it doesn't consider (or return) any cells exceeding the bottom right corner of the UsedRange of the worksheet containing the source Range against which SpecialCells is executed. Got it?
Here's a worksheet containing just one constant value in cell C3:
And here's what we get if we call SpecialCells looking for blanks using the entire worksheet as the source Range:
Instead of returning a Range representing the entire sheet, less cell C3, the resulting Range has been trimmed by SpecialCells to the worksheet's last used row and column. This trimming also takes place if the source Range overlaps the bottom and/or right boundary of the UsedRange:
Notice how SpecialCells trimmed the results on the right boundary at column C even though cells D1:D3 were explicitly included in the source Range.
It's probably not accurate to say SpecialCells "trims" the results. More likely it never actually considers cells that fall below or to the right of the UsedRange.
Here's another thing. SpecialCells ignores any source Range consisting of only one cell. When executing SpecialCells on a Range having only one cell, it will instead consider all of the cells falling within the boundary marked by the bottom right cell of the source Range sheet's UsedRange:
Finally, if SpecialCells fails to find any cells that match the specified Type and Value criteria, it does not just return Nothing, like Intersect, it raises error 1004: No cells were found.
Add all this up, toss in some arbitrary source Ranges having multiple overlapping Areas, and it gets just a little bit tricky to write a function using SpecialCells to determine whether Ranges are indeed empty empty. Tricky ain't impossible, though, so here's one way to get it done:
Function RangeIsEmpty(ByVal SourceRange As Range) As Boolean Dim SpecialUsedRange As Range Dim OverlappingRange As Range Dim OverlappingRangeArea As Range Dim EmptyCellsRange As Range Set SpecialUsedRange = SourceRange.Worksheet.Range("A1", SourceRange.SpecialCells(xlCellTypeLastCell)) Set OverlappingRange = Intersect(SourceRange, SpecialUsedRange) If OverlappingRange Is Nothing Then RangeIsEmpty = True Exit Function End If If OverlappingRange.Count = 1 Then RangeIsEmpty = IsEmpty(OverlappingRange.Value) Exit Function End If For Each OverlappingRangeArea In OverlappingRange.Areas If OverlappingRangeArea.Count = 1 Then If Not IsEmpty(OverlappingRange.Value) Then Exit Function End If Else On Error Resume Next Set EmptyCellsRange = OverlappingRangeArea.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not EmptyCellsRange Is Nothing Then If EmptyCellsRange.Count <> OverlappingRangeArea.Count Then Exit Function End If Set EmptyCellsRange = Nothing End If End If Next OverlappingRangeArea RangeIsEmpty = True End Function
Personally, I'm going to stick with COUNTA.
Another SpecialCells issue from Ron de Bruin:
ewbi.develops | 2006.03.23 09:57 AM
Actually, I'm going to stick with FormulaArray, check it out:
ewbi.develops | 2006.04.11 04:50 PM
Oops, maybe not. See the comment about FormulaArray, single quotes, and empty string values from fzz on 4/14. I confirmed it in Excel 2003. Drag.
ewbi.develops | 2006.04.14 06:26 PM
Thanks for the on error and is nothing trick! I was looking for a way to handle the case where specialcells finds nothing.
Constantin | 2009.04.26 03:03 PM
TrackBack URL: http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8347dbe0d53ef
Listed below are links to weblogs that reference Determine if an Excel Range Is Empty in VBA: