Testing for Empty Cells II
In Testing for Empty Cells, I described the IsEmpty function and how it will tell you if a cell is truly empty. If you need to determine if a whole range is empty, IsEmpty won't do the trick. IsEmpty returns TRUE if the variable passed to it is uninitialized. The Value property of a Range object meets that criterion, but the Value property of a multi-cell Range object is an array. Arrays are never empty (I think) even when there's nothing in them. Presumably this is because they are initialized when they are created. I really don't have a complete understanding of this whole process, but I do know that IsEmpty doesn't work.
There has to be something that works, though. I set up this procedure and looked at the Locals Window in the VBE.
Dim rRng As Range
Set rRng = Range("B8:B11")
Stop
End Sub
I got as far as the F's when I found the holy grail. It seems that the FormulaArray property provides the information I want. If the range actually contains an array formula, then Len(rRng.FormulaArray) will return a non-zero number. If the range contains some data, that code will return Null. If the range is empty, the return value is zero.
If IsNull(rRng.FormulaArray) Then
RangeIsBlank = False
Else
RangeIsBlank = Len(rRng.FormulaArray) = 0
End If
End Function
Eric W. Bachtal:
Great find! Last month I explored COUNTBLANK, COUNTA, and even SpecialCells for this purpose (and settled on COUNTA), but wish I'd have found this instead. Very nice.
http://ewbi.blogs.com/develops/2006/03/determine_if_a_.html
11 April 2006, 5:59 pmBeate Schmitz:
Hi,
this code counts the number of blanks in a range:
Public Sub CountBlank()
MsgBox CStr(WorksheetFunction.CountBlank(Worksheets("Tabelle1").Range("A1:A100")))
End Sub
(But ="" is counted as blank)
Regards,
12 April 2006, 3:16 pmBeate
fzz:
If I put a enter just a single quote mark in cell A1, then =RangeIsBlank(A1) returns TRUE. Worse, if I enter ="" in another cell, copy it and paste it as a value into A1, the function still returns TRUE. That doesn't seem to be what's intended.
Follow the KISS principle. Use Application.WorksheetFunction.CountA as used in the linked page in the first response above.
BTW, the RangeIsBlank function fails for cells containing zero length text constants because the FormulaArray property isn't the panacea it was supposed to be. When a range contains any mixture of blank cells and zero length text constants, .FormulaArray returns "". Since this particular problem was mentioned in the linked article 'Testing for Empty Cells', there's no excuse for failing to test for this exception with RangeIsBlank.
14 April 2006, 2:21 pmJohn Neorr:
If you are lazy and do not want to write a macro, you can put this function code directly into your excel spreadsheet to test for a "blank" cell where "blank" can be any number of spaces or a truly empty cell. The test below tests cell K9 for a "blank" and will not change the value of K9. You can substitute into the true ("Blank") and false ("Not Blank") values of the if statement below any code you want.
=IF(LEN(SUBSTITUTE(K9," ",""))=0,"Blank","Not Blank")
4 June 2008, 11:03 pm