hi,

how can i check if a cell is empty in vba? i am using an if, and woul
like to test if the cell is empty or not.

thank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

if IsEmpty(ActiveCell) then msgbox "Cell is empty"



"joao" wrote in message ...

hi,

how can i check if a cell is empty in vba? i am using an if, and would
like to test if the cell is empty or not.

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Use IsEmpty

Sub test()
If IsEmpty(Range("a1")) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"joao" wrote in message ...

hi,

how can i check if a cell is empty in vba? i am using an if, and would
like to test if the cell is empty or not.

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Ron,

PMFJI, but this seems a good place to ask about empty cells.

VBA Help for IsEmpty says that it checks for uninitialised variables, so
I've been using Range("A1").value = "" to check for an empty cell. Am I
likely to run into trouble doing it this way? Is IsEmpty preferable?

Thanks for any insight.

--
Dianne



In ,
Ron de Bruin typed:
Use IsEmpty

Sub test()
If IsEmpty(Range("a1")) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub




"joao" wrote in message
...

hi,

how can i check if a cell is empty in vba? i am using an if, and
would like to test if the cell is empty or not.

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

Depends on what you're looking for.

if you check
range("a1").value = ""

Then A1 could have a formula that evaluates to "" and this check would return
true.
=if(b132,b1,"")

If you really mean you want the cell to be empty--no value, no formula, then
isempty() is the way to go.



Dianne wrote:

Ron,

PMFJI, but this seems a good place to ask about empty cells.

VBA Help for IsEmpty says that it checks for uninitialised variables, so
I've been using Range("A1").value = "" to check for an empty cell. Am I
likely to run into trouble doing it this way? Is IsEmpty preferable?

Thanks for any insight.

--
Dianne

In ,
Ron de Bruin typed:
Use IsEmpty

Sub test()
If IsEmpty(Range("a1")) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub




"joao" wrote in message
...

hi,

how can i check if a cell is empty in vba? i am using an if, and
would like to test if the cell is empty or not.

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

--

Dave Peterson

Going even further

range("a1").Text = ""

may also give different results.

Note that it is clearer to use

range("a1").Text = vbNullString

and more effecient to use

Len(range("a1").Text) = 0

over the (very) long run.

Dave Peterson wrote in message ...
Depends on what you're looking for.

if you check
range("a1").value = ""

Then A1 could have a formula that evaluates to "" and this check would return
true.
=if(b132,b1,"")

If you really mean you want the cell to be empty--no value, no formula, then
isempty() is the way to go.



Dianne wrote:

Ron,

PMFJI, but this seems a good place to ask about empty cells.

VBA Help for IsEmpty says that it checks for uninitialised variables, so
I've been using Range("A1").value = "" to check for an empty cell. Am I
likely to run into trouble doing it this way? Is IsEmpty preferable?

Thanks for any insight.

--
Dianne

In ,
Ron de Bruin typed:
Use IsEmpty

Sub test()
If IsEmpty(Range("a1")) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub




"joao" wrote in message
...

hi,

how can i check if a cell is empty in vba? i am using an if, and
would like to test if the cell is empty or not.

thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

Excellent point -- I sometimes use formulas that evaluate to "", and I
hadn't considered this (although so far I haven't checked for empty
cells in these cases).

I shall be switching approach then. Thanks.

--
Dianne

In ,
Dave Peterson typed:
Depends on what you're looking for.

if you check
range("a1").value = ""

Then A1 could have a formula that evaluates to "" and this check
would return true.
=if(b132,b1,"")

If you really mean you want the cell to be empty--no value, no
formula, then isempty() is the way to go.


Dianne wrote:

Ron,

PMFJI, but this seems a good place to ask about empty cells.

VBA Help for IsEmpty says that it checks for uninitialised
variables, so I've been using Range("A1").value = "" to check for an
empty cell. Am I likely to run into trouble doing it this way? Is
IsEmpty preferable?

Thanks for any insight.