vba - check if cell is empty

[joao]

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

 

[Doug Broad]

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

 

[Ron de Bruin]

Use IsEmpty

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

 

[Dianne]

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

 

[Dave Peterson]

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(b1>32,b1,"")

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

 

[onedaywhen]

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.

 

[Dianne]

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.