Distinguishing empty cells (Excel 2002)

[andrewgibsonsw]

How can I distinguish between a cell having a zero value and being empty? How can I distinguish between a cell containing a zero value, and a cell containing a formula who's result is zero? Andy.

[HansV]

Since you ask in the VB / VBA forum: to test if a cell is empty, use <code>If Range("A1") = "" Then</code>. To test if a cell has a formula, use <code>If Range("A1").HasFormula Then</code>. Example:

Sub Test(aCell As Range)
If aCell = "" Then
MsgBox "Cell is empty"
ElseIf aCell = 0 Then
If aCell.HasFormula Then
MsgBox "Cell has formula resulting in 0"
Else
MsgBox "Cell contains value 0"
End If
Else
MsgBox "Cell is not empty or 0"
End If
End Sub

[andrewgibsonsw]

Thank you. I used Left(ActiveCell.Formula,1)= "=", but HasFormula is much better.

[sdckapr]

I don't know if this is an issue for Andrew, but if acell contains a null string or has a formula which results in a null string, your sub will list the cell as "empty".

I prefer to test for "blank" using IsEmpty(acell) [the VB equivalent to isblank]. This will distinguish a truly empty cell vs a cell whose value is the null string.

Steve

[Jezza]

Andrew

I know this is the VB/VBA forum and I am not sure what application you are running but a very unknown shortcut to show formulas in cells is what I call the "funny key" as no one has ever told me what its name is.

Press CTRL+ "Funny key" as depicted on the attached image. ( sorry, this was the only image I had available, but is the location of the key I am talking about)

This shows all cells which contain formulas

[HansV]

Jerry,

According to the online help, it's Ctrl+` instead of Shift+`:

<hr>CTRL+` (single left quotation mark)
Alternate between displaying cell values and displaying formulas.<hr>

The key on my US style keyboard displays the "accent grave" ` and the "tilda" ~.

[HansV]

Good point.

[Jezza]

Thanks Hans

I realised after I posted it. I was editing the text as you were replying. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Jerry