Identify Empty Cells in VBA

Whenever I need to determine if a cell is blank, I call the IsEmpty function on the Value property.

If IsEmpty(ActiveCell.Value) Then

This anonymous blogger uses vbNullString.

If ActiveCell.Value = vbNullString

In other news: In Firefox, Cntl+Click on a hyperlink and it will open that link in a new tab. Except for the aforementioned blog’s heading. When I Cntl+Click on the blog’s header link, it opens a new tab and it follows the link in the current tab. It seems to work normally for any other link on that site. I’ve never seen that behavior before.

Posted in Uncategorized

45 thoughts on “Identify Empty Cells in VBA

  1. In Firefox, click on links with your middle-mouse button to open in a new tab. But, your suggestion is news to me and very helpful when using a laptop!

  2. Is there any way create an empty (null) cell? Specifically, suppose you’re doing a calculation based on the presence/absence of data in another cell; something like =if(len(othercell)=0,””,do_calc).
    Rather than entering “”, can one make it an empty cell, e.g., if(len(othercell)=0, make_null, do_calc).

    The reason why “” is sometimes a nuisance is that cells like this can show in a pivot table as 0 instead of empty. Of course you could filter the original calculated column for blanks, then do a “clear all”. Another way I found recently was a sub where you’d select a range, then loop thru, something like: For each cell in selection, If len(Trim(cell)) = 0 Then cell = empty or cell.clear. However, I found this to run very slowly. So what would be nice to have is the ability to create empty/null cells on the fly. Or have I missed something simple (mea culpa)?

  3. A central mouse button click (assuming you have 3 mouse buttons) has always opened links in another Tab in all versions of Firefox ?

  4. In answer to jdluck’s query above, a quick test seems to indicate that vbNullString will only work in VBA and not when used with the IF function within a formula.

    It seems that for 99% of the time vbNullString is the same as “” (empty string). However, if we start using unicode or pointers then things get complicated.

    If we really wanted to follow jdluck’s example and create a make_null function we could do the following:

    1) Create a new VBA function something like this:

    Function Make_Null()
    Make_Null = vbNullString
    End Function

    2) Call this from your IF statement (e.g.

    If(A2=”Yes”,”YES”,Make_Null())

    This seems to do what we’re looking for. Comments welcome.

    Lastly, thanks to Daily Dose of Excel for linking to my new (and still rather empty) blog!

  5. vbNullString is not quite the same thing as “”, although they behave identically in VBA for all practical purposes. Under the covers, though, vbNullString is exactly what it says – a Null string. To see the difference, open the Immediate Window and compare the results of these two:

    ? StrPtr(“”)
    ? StrPtr(vbNullString)

    The first is an allocated string that just happens to have a length of zero, the second is a string with no memory allocated.

    The only time I’ve ever seen this difference become important as a practical matter is when dealing with API calls that expect a pointer to a null string, which you can only get in VBA using vbNullString.

  6. Hi Patrick –

    This depends if you need what the computer knows, or the eye sees. A cell with empty text within looks blank, but ISBLANK(cell) is false, and the answer disagrees with your eyes. In most such cases, I test if LEN(cell) = 0, in formula or VBA, which brings the computer and my eyes into agreement.

    …Michael

  7. @jgluck: “Is there any way create an empty (null) cell?”

    No. We have suggested numerous times that MS add something like a NULL() or BLANK() worksheet formula that would act as a truly blank cell. This would help with downstream calculations, charting, and presentation. We’re still waiting.

  8. MoveFirst:

    Make a line or XY chart using Make_Null in place of blank cells. A cell containing a formula returning Make_Null is treated as if it has a value of zero. A cell that is truly blank behaves differently, resulting in either a gap where the cell’s value would be plotted, or a line interpolated across the gap. There is no formulaic way to simulate a cell that is truly blank.

  9. It seems we’re all saying the same thing here but in different ways. On reflection I agree with Jon Peltier in that “there is no formulaic way to simulate a cell that is truly blank”.

    I came across this issue using VBA when a cell (formatted as a date) would not return either IsEmpty, equal to NULL or even as a number. In that case the vbNullString solved the problem.

    Rob Bovey’s comment above reminded me of some Win32 API programming in VB that I’d done years ago and I came across the following definition of vbNullString (now, I also note, repeated in the Office 2007 help file):

    vbNullString – String having value 0 – Not the same as a zero-length string (“”); used for calling external procedures

    As a VBA (or VB) string is only a pointer to a character array. The address in memory pointed to by this variable points to the start of the character array.

    The array terminates in a 2-byte null terminator and is preceded by 4 bytes showing the length of the string in bytes.

    Using “” to declare an empty string results in a character array of size 0. However, this will still contain the 4-byte length data, and the 2-byte terminator.

    vbNullString still returns a pointer but one that does not point to any character array resulting in no 4-byte length field or 2-byte terminator.

    An article by Microsoft (of which I have a printed copy but now can’t find the URL) on VBA optimization suggests that vbNullString would execute 50% faster than “”.

    Having said all that I return to my original comment – use what works for you in the situation you’re faced with. vbNullString saved the days for me so may help others.

  10. can you create a null cell (as a function result)?
    no I don’t think so, by definition, by putting something in it (the formula) you are changing it from a null cell to some other type. That seems fair enough to me. =NA() is the next best thing for graphs etc.

    If you enter =A2 in A1 where A2 is empty A1 returns 0. Excel coerces that empty cell to a number of value zero. If it didn’t, blanks would propagate all over and break select special stuff for example.

    If you change A2 to be =”” then A1 looks blank, but its value is really a zero length string. If you paste the value over A2 you get a cell that looks and feels blank, nothing in the formula bar etc. But it is not a blank cell its a string cell with a zero length string. This means its not ISBLANK() and its not isEmpty() but it is the equivalent of vbNullString.

    I tend to use if len(cl.value)=0 then…

  11. If you are testing for a blank in VBA, this is faster:

    If Len(rCell.Value) = 0 Then

    than this:

    If rCell.Value = “”

    because VBA determines the length of a string as the first step in getting the string itself. That doesn’t necessarily help with rCell.Value, which trips out to the worksheet, but it does help with a string variable in memory and it also helps with “”.

  12. totally off topic, but I just picked up a VBA book I haven’t touched in a while and I learned a something new…
    the Evaluate() method.

    instead of:
    Dim n as Long

    n = worksheetfunction.Sum(sheet1.[A1:A2].cells)

    Msgbox n

    you can write:

    MsgBox Evaluate(“Sum(A1:A2)”)

  13. It’s best to use Evaluate, as well as the [] modifiers, only in the Immediate window. For one thing, I’ve heard that the brackets are slower than the normal VBA referencing. For another, and more important, unless you are sure that the active sheet contains the cells you want to sum, you will not get what you expect.

  14. As Evaluate seems to be the equivalent of the use of brackets I’d think this will do better

    MsgBox WorksheetFunction.Sum([Blad3!A1:A3])

    It’s possible to point at the sheet between brackets; Jon’s second argument is therefore irrelevant.
    I’ve never been able to establish any speed difference in using/not using brackets.

  15. The result of continued testing:

    MsgBox Evaluate(“Sum(Blad3!A1:A3)”)

    None of Jon’s objections apply to this code.

  16. So let me get this straight, no difference in performance in using [Sheet1!A1:A2] vs Sheet1.Range(“A1:A2?)?

  17. Sorry for my English..

    ‘True for really empty
    CellIsReallyEmpty = IsEmpty(ActiveCell)
       
    ‘True for ;;; or “”
    ‘respect zero hidding
    CellForEyesEmpty = Len(ActiveCell.Text) = 0

    And yes, vbNullsString is not the same as zero lenght string “” (storage difference, string pointers)

  18. And my tip for “first empty cell from bottom”:

    ColumnNumber = 1
    With Cells(Rows.Count, ColumnNumber).End(xlUp)
    .Offset(Sgn(.Row - 1), 0).Select
    End With

  19. Can anyone explain this code:

    Sub c()
    Dim v As Variant, i As Long, j As Long
    v = Range(“A1:Z10000?).Value
    For i = LBound(v, 1) To UBound(v, 1)
    For j = LBound(v, 2) To UBound(v, 2)
    v(i, j) = Trim(v(i, j))
    Next
    Next
    Range(“A1:Z10000?).Value = v
    End Sub

    Why does the variable “v” have parameters??

  20. MacroMan:

    v is being used to hold an array rather than a single variable. The code is using i and j to iterate through the rows and columns of the array and Trim (remove trailing spaces from) each item in it. Then it writes the contents of v (the entire array) back out to the range that it got it from.

  21. ahhhhh, I think the Variant data type confused me. It could have also been “Dim v() as Variant”. That would have been clearer. I forget this language is not so consistent. Thanks Stephanie!

    MacroMan
    qualifier41@yahoo.com

  22. I’m running into a similar issue w/empty cells, in that I’m trying to copy one column of data from one sheet to another.

    However, the column in the first sheet may or may not have blank cells (identified by “”), depending on parameters set by user – and what I’d like to do is set up code that always pastes the actual data in consecutive rows.

    Example:

    First Sheet

    A
    1 12344
    2 “”
    3 “”
    4 4525
    5 4565
    6 “”

    Second Sheet (desired output)

    A
    1 12344
    2 4525
    3 4565

    Any ideas?

  23. Simon,

    Whoa, how in the world did you know that!? Yes it is, the no “()” in the variant Dim statement confused me. I read your blogs, but I haven’t been playing around with VBA code in a while.

  24. And going back to MacroMan question on variant arrays they are impressively quicker too as this demo will show:

    Sub TryThis()

        Dim rng As Range
        Dim tbl As Range
        Dim tbl2 As Variant
        Dim start As Double
        Dim i As Long, j As Long
        Dim arr(0 To 19999)

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

        ”’set up some work
       Sheets(1).Cells.ClearContents
        Set tbl = Range(“A2:A20000”)
        For Each rng In tbl
            rng = ” as range “
            rng.Offset(, 3) = ” as array “
            rng.Offset(, 6) = ” as varnt “
        Next

        ”’as range object
       Set tbl = Range(“A2:A20000”)
        start = Timer
        For Each rng In tbl
            rng.Offset(, 1) = Trim(rng)
        Next
        Debug.Print “as range”, Timer – start

        ”’as array
       Set tbl = Range(“D2:D20000”)
        start = Timer
        For i = 0 To 19999
            arr(i) = Cells(i + 2, “D”)
        Next
        For i = 0 To 19999
            Cells(i + 2, “E”) = Trim(arr(i))
        Next
        Debug.Print “as array”, Timer – start

        ”’as variant array
       tbl2 = Range(“G2:G20000”)
        start = Timer
        For j = LBound(tbl2, 1) To UBound(tbl2, 1)
            tbl2(j, 1) = Trim(tbl2(j, 1))
        Next j
        Range(“H2:H20000”) = tbl2
        Debug.Print “as varnt”, Timer – start

        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic

    End Sub

  25. Thanks Geoff, like I said I haven’t been playing with VBA in a while. It would have been clearer if the Dim statement said “v() as Variant”, note the “()”. Here is the same method in C# on a Form:

    private void button1_Click(object sender, EventArgs e)
    {
    foreach (Control ctl in this.Controls)
    {
    string ctltype = ctl.GetType().ToString();
    if (ctltype == “System.Windows.Forms.TextBox”)
    {
    ctl.Text = ctl.Text.Trim();
    }
    }
    }

  26. Your firefox issue seems to be a Javascript – many websites use javascript on “a” tags to do … things. When you control click, it opens as a new tab AND runs the javascript. A tags should have href (or name) variables to support web browsers which don’t have Javascript (and also, search bots which might not understand JS).

    Bascially, another example of Javascript being evil because someones written something evil in it…

  27. Re: Macroman, Hans Schraven and Peltier discussion on Brackets.
    Microsoft had a KB article which stated that using brackets for referencing ex: [A1:A10] (what theycalled tunneling as I recall) is slower than using references such as Range(“A1:A10?). This is for referencing ranges such as [A1:A10].Value = 1. I can’t say for executing a worksheet function such as [Sum(A1:A10)]

  28. vbNullString is handy when you need to test if a cell is empty, because my macro was not making the difference between 0 and an empty cell.

    This sucks ;d

  29. Use four quotes for If() Statements inserting into cells:

    Sheets(“summary report”).Range(“p6”).FormulaR1C1 = “=IF(RC[-1]0,RC[-1]*RC[1],””””)”

    The above formula successfully achieves a blank cell shown in Excel when the if statement is false.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.