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
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
Is A Cell Empty?
In a worksheet, the best way to check is a cell is empty is to use the
ISBLANK() worksheet function:
Row 3 indicates what is in the corresponding column in row 4.
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
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?
COUNTBLANK() worksheet function does not behave in a consistent manner with its
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:
An easy way to check if a cell is blank from VBA is to call the
Range.Value2) property and compare the result to the
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
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.
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
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?
Perhaps I’m an old fuddy duddy too, but yes. It’s annoying. Great explanation of these subtleties though. ++
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.]
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.
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.
If I understand your question correctly, the easiest thing to do would be to use the COUNTBLANK() function?
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?
Conditional formulas treat blank cells and empty cells differently. AVERAGE(c10:c17) will ignore empty cells but AVERAGE(if(b2:b50=2005,c2:c50)) will treat them as zeros. Both formulas will ignore blank cells. I was surprised to discover this today after many years of using Excel.
I am going to try this tomorrow at work with a very frustrating problem I’ve been having with a VBA project I manage, but this is the first place I’ve found a satisfactory description of the problem that I’m seeing and a possible way to work around it. I’m glad I found this. There are too damn many little inconsistencies and problems with Excel when you really get to know the program well. Thank you very much for this post.