Tips & Tricks 124 – VBA – Check if a Range is Blank When Range Contains Formulas returning Blanks

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

Sub CheckifBlank()
    If WorksheetFunction.CountA(Range("A1:A100")) Then
        MsgBox "Range is Not Blank"
        Else
        MsgBox "Range is Blank"
    End If
End Sub

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

Sub TestBlanks()
    If Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100")) Then
        MsgBox "All Blanks"
        Else
        MsgBox "Not All Blanks"
    End If
End Sub