Results 1 to 7 of 7

Difference between IsEmpty and IsBlank and ="" ??

This is a discussion on Difference between IsEmpty and IsBlank and ="" ?? within the Excel Questions forums, part of the Question Forums category; Could someone please tell me the difference between IsEmpty and isBlank and =""? I got a type mismatch error in ...

  1. #1
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    408

    Default Difference between IsEmpty and IsBlank and ="" ??

    Could someone please tell me the difference between IsEmpty and isBlank and =""? I got a type mismatch error in one place where I had used ="". When I changed it to isEmpty(activecell) my macro ran fine.

    I'm trying to learn and do things the right way, and I'm not sure why it works now, and whether or not I should fix my other instances of ="" even thought the code is working. When is each use appropriate?

    In case it's relevant, USUALLY (though not always) I'm trying to test to see if my query returned anything.

    Thanks!

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    Depends on what you're testing...

    ISEMPTY is for testing if a variable has a value assigned to it...
    If a value has been assigned to the variable (even a value of "") then it is NOT empty. If no value has been assigned to the variable, it IS Empty.

    If you use ISEMPTY to test a RANGE, it works the same as ISBLANK.

    IsBlank and ="" should be used to test if a RANGE is blank.
    ="" will count Forumula Blanks as blank, where IsBlank will not. IsBlank will consider a formula blank as NOT blank.

    Hope that clears up a little..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    408

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    Quote Originally Posted by jonmo1 View Post
    Depends on what you're testing...

    ISEMPTY is for testing if a variable has a value assigned to it...
    If a value has been assigned to the variable (even a value of "") then it is NOT empty. If no value has been assigned to the variable, it IS Empty.

    If you use ISEMPTY to test a RANGE, it works the same as ISBLANK.

    IsBlank and ="" should be used to test if a RANGE is blank.
    ="" will count Forumula Blanks as blank, where IsBlank will not. IsBlank will consider a formula blank as NOT blank.

    Hope that clears up a little..
    Thanks. What you say makes good sense, but it doesn't seem to match what I was seeing.

    My macro had inserted subtotals, and I was trying to make a subtotal description appear in a cell that would normally be blank because the subtotalling feature put the description in a column I wasn't displaying. There doesn't appear to be a formula in that cell, but ="" didn't work and isEmpty did.

    Code:
     
    Dim Rng As Long
    Dim i As Long
    Rng = Range(Range("b16"), Range("b16").End(xlDown)).Offset(0, 2).Rows.Count - 1
    Range("d16").Activate
    For i = 1 To Rng
    If IsEmpty(ActiveCell) Then    'You can replace "" with 0 to delete rows with 'the value zero
        ActiveCell.Value = "Phase " & ActiveCell.Offset(0, -2) & ":"
        ActiveCell.HorizontalAlignment = xlRight
        ActiveCell.Offset(1, 0).Activate
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Next i
    Or maybe I'm not understanding what you mean by "formula blank"?

    Thanks,
    Jennifer
    Last edited by 9tanstaafl9; Nov 12th, 2008 at 12:26 PM. Reason: forgot to paste my code in

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,985

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    Quote Originally Posted by 9tanstaafl9 View Post
    Thanks. What you say makes good sense, but it doesn't seem to match what I was seeing.

    My macro had inserted subtotals, and I was trying to make a subtotal description appear in a cell that would normally be blank because the subtotalling feature put the description in a column I wasn't displaying. There doesn't appear to be a formula in that cell, but ="" didn't work and isEmpty did.

    Code:
     
    Dim Rng As Long
    Dim i As Long
    Rng = Range(Range("b16"), Range("b16").End(xlDown)).Offset(0, 2).Rows.Count - 1
    Range("d16").Activate
    For i = 1 To Rng
    If IsEmpty(ActiveCell) Then    'You can replace "" with 0 to delete rows with 'the value zero
        ActiveCell.Value = "Phase " & ActiveCell.Offset(0, -2) & ":"
        ActiveCell.HorizontalAlignment = xlRight
        ActiveCell.Offset(1, 0).Activate
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Next i
    Or maybe I'm not understanding what you mean by "formula blank"?

    Thanks,
    Jennifer
    A2 left unused

    A3 houses

    =""

    which is a formula blank.

    B2, copy down:

    =ISBLANK(A2)

    C2, copy down:

    =A2=""

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    "formula blank" is a formula that returns blank on one condition, but something else on a nother condition, like

    =IF(A1="","",A1)

    If A1 ="" then the formula will also return ""
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    408

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    Quote Originally Posted by jonmo1 View Post
    "formula blank" is a formula that returns blank on one condition, but something else on a nother condition, like

    =IF(A1="","",A1)

    If A1 ="" then the formula will also return ""
    Thank you...

  7. #7
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    408

    Default Re: Difference between IsEmpty and IsBlank and ="" ??

    Quote Originally Posted by Aladin Akyurek View Post
    A2 left unused

    A3 houses

    =""

    which is a formula blank.

    B2, copy down:

    =ISBLANK(A2)

    C2, copy down:

    =A2=""
    At first I thought you wrote some sort of weird Excel Haiku, but I realized you were telling me to do something, which I did and now it all makes sense. Thanks.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com