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:

?ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Address
$A$1:$C$2,$A$3:$B$3

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:

?ActiveSheet.Range("C1:D3").SpecialCells(xlCellTypeBlanks).Address
$C$1:$C$2

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:

?ActiveSheet.Range("A1").SpecialCells(xlCellTypeBlanks).Address
$A$1:$C$2,$A$3:$B$3

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.