vba - check if cell is empty

  • Thread starter Thread starter joao
  • Start date Start date
J

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
 
Use IsEmpty

Sub test()
If IsEmpty(Range("a1")) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub
 
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
 
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.
 
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.
 
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.
 
Back
Top