Is Your Cell Blank Or Empty?

Two pet peeves of mine are the way the terms [unique and distinct] and [empty and blank] are interchangeably used in Excel. They have different meanings so it’s really confusing when the name of a function or utility suggests it will do one thing when, in fact, it does the other.

I’ve defined unique and distinct on previous posts such as this one, so I’ll put those to one side and concentrate today’s rant post on empty and blank. Before I highlight the inconsistencies between ISBLANK() , COUNTBLANK() and VBA.IsEmpty() , here are my definitions of empty and blank cells:

  • An empty cell can be defined as a cell that contains absolutely nothing: no constant, no formula and no prefix character.
  • A blank cell can be defined as a cell which may be empty, or may contain a prefix character or a null string (formula result or constant).

Note that the formatting is ignored. These definitions are just my own – so please don’t take them as gospel – but they’re based on the behaviour of VBA.IsEmpty() and COUNTBLANK().

Is A Cell Empty?

In a worksheet, the best way to check is a cell is empty is to use the ISBLANK() worksheet function:

ISBLANK_1

Row 3 indicates what is in the corresponding column in row 4.

The Range.Value and Range.Value2 properties return a Variant/Empty when the given cell is empty, so the best way to check if a cell is empty in VBA is to use the VBA.Information.IsEmpty() function on their output:

Sub foo()

    Debug.Print IsEmpty(Sheet1.Range("A4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("B4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("C4").Value2)   'False
    Debug.Print IsEmpty(Sheet1.Range("D4").Value2)   'True

End Sub

The ISBLANK() worksheet function and VBA.IsEmpty() give exactly the same results so, unless one considers blank and empty to mean the same thing, they’re obviously inconsistently named. By my own definition, ISBLANK() should’ve been called ISEMPTY(). It’s worth mentioning that the ISBLANK() worksheet function isn’t available in VBA via the Application.WorksheetFunction class: presumably because Microsoft recognised that its functionality was already covered by VBA.IsEmpty() which is faster to call from VBA.

Is A Cell Blank?

The COUNTBLANK() worksheet function does not behave in a consistent manner with its ISBLANK() counterpart. COUNTBLANK() will count empty cells, cells with a null string and cells which contain a prefix character. Yeah, I know, it’s pretty confusing.  The upside is we can use COUNTBLANK() to check if a cell is actually blank:

COUNTBLANK_1

An easy way to check if a cell is blank from VBA is to call the Range.Value (or Range.Value2) property and compare the result to the VBA.Constants.vbNullString constant:

Sub foo()

    Debug.Print IsBlank(Sheet1.Range("A4"))  'False
    Debug.Print IsBlank(Sheet1.Range("B4"))  'True
    Debug.Print IsBlank(Sheet1.Range("C4"))  'True
    Debug.Print IsBlank(Sheet1.Range("D4"))  'True

End Sub

Public Function IsBlank(ByRef rngToCheck As Range) As Boolean
    IsBlank = (CStr(rngToCheck.Cells(1).Value2) = vbNullString)
End Function

It’s more efficient within VBA to do it this way than to call the Application.WorksheetFunction.CountBlank() method.

Does A Cell Contain A Null String?

I’ve mentioned null strings a few times so I better explain what they are. A null string is a zero-length string that could be a constant or the result of a formula. For example, this formula returns a null string:

=""

If you copy that formula and paste special values then the cell will contain a constant null string. You can also sometimes get null strings when you import data from an external source, so they’re worth knowing about.

The HasNullString() function below will return True if a cell contains a null string. If you want formula results to be ignored (ie. check for constants only) then pass True into the blnConstantsOnly parameter . If the cell has a prefix character then the function will return False.

Public Function HasNullString( _
                ByRef rngToCheck As Range, _
                Optional ByVal blnConstantsOnly As Boolean = False _
                            ) As Boolean

    Dim rngFirstCell As Range
    Dim strToCheck As String
    Dim varToCheck As Variant

    Set rngFirstCell = rngToCheck.Cells(1)
    varToCheck = rngFirstCell.Value2

    If Not IsEmpty(varToCheck) Then

        If blnConstantsOnly Then
            strToCheck = rngFirstCell.Formula
        Else
            strToCheck = CStr(varToCheck)
        End If

        If strToCheck = vbNullString Then
            HasNullString = (LenB(rngFirstCell.PrefixCharacter) = 0)
        End If

    End If

End Function

So, am I just being an old fuddy-duddy or do these inconsistencies annoy you too?

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , , , , . Bookmark the permalink.

6 Responses to Is Your Cell Blank Or Empty?

  1. Perhaps I’m an old fuddy duddy too, but yes. It’s annoying. Great explanation of these subtleties though. ++

    Like

  2. Lori says:

    Yup, just one of many inconsistencies in Excel terminology. My understanding is that “Empty” is a VBA variant type and “Blank” is a native Excel type (xltypeNil) and they map to each other. Also a cell with only a prefix character is the same as a null string – the prefix character can be removed by clearing formats, so there’s really just the two cases to consider: True blanks and null strings. =COUNTBLANK(Rng) doesn’t distinguish the two whereas =COUNTIF(Rng,”=”) and =COUNTIF(Rng,””) do.

    I came across a related issue recently (http://stackoverflow.com/questions/19840989/is-there-a-function-that-would-return-when-input-ref-is-empty-and-its-conte). The question essentially asked if you can have a formula that returns an array of blanks so that the selected cell values will be ignored in formulas like MIN. Most functions like IF will convert blanks either to zeros or to null strings and i didn’t think it was possible to preserve the blank values inside arrays. But after stumbling upon an array evaluation trick on the excelxor.com blog it appears returning blanks in arrays is possible via the INDEX or CELL functions (as suggested in the latest SO response and the comment in the linked post).

    Great work on formula extensions by the way! [i have been trying to get it to work in my Excel 2013.]

    Like

    • Colin Legg says:

      Thanks, Lori. :-)

      The formula extensions works absolutely fine on my 32-bit Excel 2013. I coded for 64-bit but didn’t didn’t have an available environment to test it. If you have any problems (or suggestions) then please post a comment on the post and I’ll try to help.

      Like

  3. Kaleb Christoffersen says:

    Thanks for the post. I found it insightful. Regarding your “Is A Cell Blank?” section, if I wanted to check an entire column or entire row how would the sub or function need to be modified to account for this range instead of only a single cell? Thanks again. Great post.

    Like

    • Colin Legg says:

      If I understand your question correctly, the easiest thing to do would be to use the COUNTBLANK() function?

      Like

      • Kaleb Christoffersen says:

        So I tried that but it’s still missing some columns that are blank all but for some null string value or prefix character. The dataset is too large to figure which cell(s) this is occurring in.

        I have a sheet of data that I loop through the columns from lastColumn to 1 at step -1. If the entire column is blank (like the results show for single cell in your “Is A Cell Blank?” use case), I want to delete the column. If all cells in the column are blank, empty, null string, and/or prefix character I want to delete it ultimately.

        It seems to me the function: IsBlank = (CStr(rngToCheck.Cells(1).Value2) = vbNullString could be modified to account for broader ranges as opposed to single cells. Thoughts?

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s