Difference between IsEmpty and IsBlank and ="" ??

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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..
 
Upvote 0
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:
Upvote 0
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=""
 
Upvote 0
"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 ""
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top