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
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
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,
Beate
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.
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”)
For me it works perfectly under the following preconditions:
– speed is paramount: the test of FormulaArray appears to be in constant time
– being ’empty’ is defined as being blank: no value is produced
I use your code snippet in combination with a binary search to crop outdated and oversized usedranges. This is very fast indeed, checking huge arrays in fractions of seconds. In my application, i’m quite happy to get rid of spurious single quotes and invisible formulas along the way.
Unfortunatly, this function causes an error if the range has “Protection – Hidden” set and the sheet is protected. I’ve not yet looked into finding a solution to this but hopefully there is one.
Actually on investigation, there is only an error if every cell in the range is not empty. Therefore we can just use basic error trapping since errors only occur when RangeIsBlank = false:
Function RangeIsBlank(rRng As Range) As Boolean
On Error Resume Next
If IsNull(rRng.FormulaArray) Then
RangeIsBlank = False
Else
RangeIsBlank = Len(rRng.FormulaArray) = 0
End If
On Error GoTo 0
End Function
If you are willing to accept the reverse function (one that test if a range is not blank), then this 2-line function should work…
On Error Resume Next
RangeNotBlank = rRng.Find(What:=“*”, LookIn:=xlFormulas).Row <> 0
End Function
Note: You cannot simple “Not” the statement trying to produce a RangeIsBlank function. If you really want the RangeIsBlank function, then it will take a third line to do that using the above approach…
On Error Resume Next
RangeIsBlank = rRng.Find(What:=“*”, LookIn:=xlFormulas).Row
RangeIsBlank = Err.Number
End Function
Just a follow up on my last posting. First, there is no need to perform the “not equal to zero” test in my RangeNotBlank function (just setting the Row number to the Boolean RangeNotBlank function name is sufficient)…
On Error Resume Next
RangeNotBlank = rRng.Find(What:=“*”, LookIn:=xlFormulas).Row
End Function
Second, some may consider the following 3rd statement in my alternative RangeIsBlank function to be the “more normal” way to produce the correct True/False values for the function (as opposed to my Err.Number assignment)…
On Error Resume Next
RangeIsBlank = rRng.Find(What:=“*”, LookIn:=xlFormulas).Row
RangeIsBlank = Not RangeIsBlank
End Function
By way of explanation, for those that have followed any of my online responses to newsgroup questions, I tend to favor compact code (minimum line count) wherever possible (with one-liners being the ultimate “prize” in this endeavor).
2 one-liners:
emptycolumn = Join(WorksheetFunction.Transpose(rng), “”) = “”
End Function
emptyrow = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(rng)), “”) = “”
End Function
@hans,
Those functions do not work the same way as the code in the blog article… if any cell in the range contains a formula that evaluates to the empty string (“”), then your functions will return TRUE whereas the case for Column D in the blog article shows FALSE is the desired returne value (the function should test for a truly empty range, not one that just looks empty). I would also note that neither of your functions will work on a 2-D range (such as B3:F9).
@Rick
The names of these functions didn’t suggest they would apply to multidimensional ranges.
So I suppose you would prefer:
On Error Resume Next
leeg = rng.Address = rng.SpecialCells(4).Address
If IsEmpty(leeg) Then leeg = False
End Function
@hans,
I may have mistaken why you posted what you did in the last message of yours that I responded to. Since I had indicated my preference for one-liners in the message before that, and since you started your message by saying “2 one-liners”, I figured you were responding to what you thought might have been an “implied challenge”. That is why I responded the way I did… to point out that your one-liners functioned differently than the blog article laid out. The comment about 2D ranges was an aside… had your one-liners worked as the blog article indicated they should, I would have been happy to acknowledge them. As for your last posted code (function name “leeg”… by the way, what does leeg stand for?), it won’t work as a UDF… for whatever reason, SpecialCells will not work correctly in a UDF. Even if it did, you might still not want to use it since SpecialCells limits itself to the UsedRange and it would be possible for a user to specify a range argument to your function that goes past the UsedRange’s limits… this would probably cause the range argument’s address to be different from the SpecialCell’s return address.
Beate Schmitz,
Your approach was very nice, I have used it as inspiration for a bigger macro.
Thanks,
vicsar