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.

Sub LookAtRange()
   
    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.

img: formulas of various ranges

img: values of various ranges

Function RangeIsBlank(rRng As Range) As Boolean
   
    If IsNull(rRng.FormulaArray) Then
        RangeIsBlank = False
    Else
        RangeIsBlank = Len(rRng.FormulaArray) = 0
    End If
   
End Function
Posted in Uncategorized

14 thoughts on “Testing for Empty Cells II

  1. 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

  2. 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.

  3. 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. 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.

  5. 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.

  6. 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

  7. 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…

    Function RangeNotBlank(rRng As Range) As Boolean
      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…

    Function RangeIsBlank(rRng As Range) As Boolean
      On Error Resume Next
      RangeIsBlank = rRng.Find(What:=“*”, LookIn:=xlFormulas).Row
      RangeIsBlank = Err.Number
    End Function
  8. 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)…

    Function RangeNotBlank(rRng As Range) As Boolean
      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)…

    Function RangeIsBlank(rRng As Range) As Boolean
      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).

  9. 2 one-liners:

    Function emptycolumn(rng As Range)
        emptycolumn = Join(WorksheetFunction.Transpose(rng), “”) = “”
    End Function
    Function emptyrow(rng As Range)
        emptyrow = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(rng)), “”) = “”
    End Function
  10. @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).

  11. @Rick
    The names of these functions didn’t suggest they would apply to multidimensional ranges.

    So I suppose you would prefer:

    Function leeg(rng As Range)
     On Error Resume Next
     leeg = rng.Address = rng.SpecialCells(4).Address
     If IsEmpty(leeg) Then leeg = False
    End Function
  12. @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.

Leave a Reply

Your email address will not be published. Required fields are marked *