Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank cell
(True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly blank?
Thanks for your help. Otto

isempty has always worked for me when checking a range.

I don't recall seeing that warning, but there are lots of posts to the
newsgroups.



Otto Moehrbach wrote:

Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank cell
(True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly blank?
Thanks for your help. Otto

--

Dave Peterson

I realize that this check is good only for a blank cell, not for a cell
that contains a formula that returns a blank cell

I have to disagree, Otto. When I run this code:

If ActiveCell.Value = "" Then Beep

when a cell that has this formula in it is selected:

=IF(TRUE,"","")

I get a beep.

--
Jim
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank
cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto

It appears that IsEmpty() can distinguish blank from empty:

Sub Macro1()
MsgBox (IsEmpty(Cells(1, 1).Value))
End Sub

will anounce false if A1 contains:

=IF(1=1,"","")


--
Gary's Student


"Otto Moehrbach" wrote:

Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank cell
(True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly blank?
Thanks for your help. Otto

And after reading Jim Rech's response, I want to clarify mine.

isempty(range("a1")) will return true if there's no formula and no constant in
that cell.

isempty(range("a1")) will return false if there's a formula in that cell--no
matter what it returns.

And to make matters slightly worse, if you convert a formula that evaluated to
"" to a value, then the formula isempty(range("a1")) will return false.

But if range("a1").value = "" won't care if the cell is empty or evaluates to ""
(or used to be a formula that evaluate to "" and was converted to values).
It'll return True.



Dave Peterson wrote:

isempty has always worked for me when checking a range.

I don't recall seeing that warning, but there are lots of posts to the
newsgroups.

Otto Moehrbach wrote:

Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank cell
(True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly blank?
Thanks for your help. Otto

--

Dave Peterson

--

Dave Peterson

Jim
I didn't word my statement correctly. I meant just what you said,
namely that the If ....."" Then is True if the cell is blank, regardless of
why it is blank. Otto
"Jim Rech" wrote in message
...
I realize that this check is good only for a blank cell, not for a cell
that contains a formula that returns a blank cell

I have to disagree, Otto. When I run this code:

If ActiveCell.Value = "" Then Beep

when a cell that has this formula in it is selected:

=IF(TRUE,"","")

I get a beep.

--
Jim
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for
a blank cell, not for a cell that contains a formula that returns a blank
cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto

Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only for a
blank cell, not for a cell that contains a formula that returns a blank
cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above (returns
False if it contains the formula). However, I've seen on this newsgroup
that "IsEmpty" should only be used to determine if a variable has been
initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto

Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/arch...r-empty-cells/

I don't think it necessarily adds anything to what was said here, but it
may.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only
for a blank cell, not for a cell that contains a formula that
returns a blank cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above
(returns False if it contains the formula). However, I've seen on
this newsgroup that "IsEmpty" should only be used to determine if a
variable has been initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto

Dick
That's a good write-up. Thanks for that.
I have one question I want to ask to keep me out of trouble with this in
the future. You say, as have others, that IsEmpty, for the purposes
discussed in this thread, should act on the Value property of the range
object. And that works. But I have always used it (in ignorance???) on the
range object itself, and not on the Value property of the range object. Am
I liable to step in something by doing this? Thanks again. Otto
"Dick Kusleika" wrote in message
...
Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/arch...r-empty-cells/

I don't think it necessarily adds anything to what was said here, but it
may.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only
for a blank cell, not for a cell that contains a formula that
returns a blank cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above
(returns False if it contains the formula). However, I've seen on
this newsgroup that "IsEmpty" should only be used to determine if a
variable has been initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto

I've been one of the offenders who used
if isempty(somerange) then

But after reading Dick's blog, I'm gonna (try to) change to:
if isempty(somerange.value) then

From an English point, it always made more sense to me to talk about the
container (the cell) than the value. But looking at Dick's screen print, I see
the error of my ways.

But as a saving grace, .value is the default property of a range.



Otto Moehrbach wrote:

Dick
That's a good write-up. Thanks for that.
I have one question I want to ask to keep me out of trouble with this in
the future. You say, as have others, that IsEmpty, for the purposes
discussed in this thread, should act on the Value property of the range
object. And that works. But I have always used it (in ignorance???) on the
range object itself, and not on the Value property of the range object. Am
I liable to step in something by doing this? Thanks again. Otto
"Dick Kusleika" wrote in message
...
Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/arch...r-empty-cells/

I don't think it necessarily adds anything to what was said here, but it
may.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good only
for a blank cell, not for a cell that contains a formula that
returns a blank cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above
(returns False if it contains the formula). However, I've seen on
this newsgroup that "IsEmpty" should only be used to determine if a
variable has been initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is truly
blank? Thanks for your help. Otto



--

Dave Peterson

Otto: I agree with everything Dave said. When you leave off the .Value
property, you are still testing the .Value property because it's the default
of the Range object. There are two potential pitfalls to omitting Value:
MS could change the default property or Range; You force the compiler to
look up the default property which is a performance hit.

The performance hit, I'll bet, is so small that you couldn't measure it in a
10,000 line program and the chance that MS will change the default property
is even less. So I think you're safe leaving it off. I tried to get into
the habit of always specifiying the default property a couple of years ago,
which is why I use it.


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Dick
That's a good write-up. Thanks for that.
I have one question I want to ask to keep me out of trouble with
this in the future. You say, as have others, that IsEmpty, for the
purposes discussed in this thread, should act on the Value property
of the range object. And that works. But I have always used it (in
ignorance???) on the range object itself, and not on the Value
property of the range object. Am I liable to step in something by
doing this? Thanks again. Otto "Dick Kusleika"
wrote in message
...
Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/arch...r-empty-cells/

I don't think it necessarily adds anything to what was said here,
but it may.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good
only for a blank cell, not for a cell that contains a formula that
returns a blank cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above
(returns False if it contains the formula). However, I've seen on
this newsgroup that "IsEmpty" should only be used to determine if a
variable has been initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is
truly blank? Thanks for your help. Otto

Dave & Dick
I've resolved to always state the .Value also. Thanks for your help.
Otto
"Dick Kusleika" wrote in message
...
Otto: I agree with everything Dave said. When you leave off the .Value
property, you are still testing the .Value property because it's the
default of the Range object. There are two potential pitfalls to omitting
Value: MS could change the default property or Range; You force the
compiler to look up the default property which is a performance hit.

The performance hit, I'll bet, is so small that you couldn't measure it in
a 10,000 line program and the chance that MS will change the default
property is even less. So I think you're safe leaving it off. I tried to
get into the habit of always specifiying the default property a couple of
years ago, which is why I use it.


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Dick
That's a good write-up. Thanks for that.
I have one question I want to ask to keep me out of trouble with
this in the future. You say, as have others, that IsEmpty, for the
purposes discussed in this thread, should act on the Value property
of the range object. And that works. But I have always used it (in
ignorance???) on the range object itself, and not on the Value
property of the range object. Am I liable to step in something by
doing this? Thanks again. Otto "Dick Kusleika"
wrote in message
...
Hey Otto:

I didn't a little analysis of IsEmpty that you can see here

http://www.dailydoseofexcel.com/arch...r-empty-cells/

I don't think it necessarily adds anything to what was said here,
but it may.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Otto Moehrbach wrote:
Thanks Dave, Jim, and the Student. That clears it up for me. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've been using
If Range(....)="" Then
to check if a cell is blank. I realize that this check is good
only for a blank cell, not for a cell that contains a formula that
returns a blank cell (True in both cases).
I've also used:
If IsEmpty(Range(....)) Then
and this has been working well including the formula case above
(returns False if it contains the formula). However, I've seen on
this newsgroup that "IsEmpty" should only be used to determine if a
variable has been initialized.
Can anyone clarify the use of IsEmpty to determine if a cell is
truly blank? Thanks for your help. Otto