Truly Empty Cells

Topic

New Reply

It is common in Excel to use “” to give a cell a blank value, as in =IF(ISNA(A1),””,A1). The problem is that the cell looks empty, but it isn’t truly empty. The COUNTBLANK function will count it as a blank cell, but if you test it with ISBLANK it comes up false. Is there any way to substitute something other than “” to make the cell truly empty?

Reply | Quote

Replies

Please refered to this Thread #12, regarding blank cell and formula blank cell

Blank Excel Cells

Regards
Bosco

Reply | Quote

Is there any way to substitute something other than “” to make the cell truly empty?

No, there is no way to make a formula return a “completely blank” value; the empty string “” is the best you can do.
Instead of testing the result with ISBLANK(cell), you can use cell=””. This will catch both really blank cells and cells that contain an empty string.

Reply | Quote

No, there is no way to make a formula return a “completely blank” value; the empty string “” is the best you can do.
Instead of testing the result with ISBLANK(cell), you can use cell=””. This will catch both really blank cells and cells that contain an empty string.

Thanks, Hans. I expected that was the case, but I thought maybe I had missed something. It would be nice if they added a function to Excel to completely zap a cell (like Clear > All from the Edit menu). Unitl they do that, I’ll just have to do as you recommend and avoid ISBLANK.

Reply | Quote

I don’t think Microsoft will do that. A cell containing a formula is not blank, even if the formula returns an empty string.

Reply | Quote

What I have done on occasion is to have the cell give an error [like na()] rather than the null. Then used edit- goto formulas and check just errors and onece they are selected you can hit to clear them all.

Steve

Reply | Quote

What I have done on occasion is to have the cell give an error [like na()] rather than the null. Then used edit- goto formulas and check just errors and onece they are selected you can hit to clear them all.

Steve

Excellent idea, Steve. I hadn’t thought about that. It actually makes it easier because I don’t have to check for #N/A – I can just leave them and zap those cells with your procedure.

Reply | Quote

Cell zapping is done with the .ClearContents method, as in: Selection.ClearContents.

–Scott.

Reply | Quote

Cell zapping is done with the .ClearContents method, as in: Selection.ClearContents.

–Scott.

Scott,
You’re right, but in this case I wasn’t going to use VBA. Just formulas and manual clearing.

Bill

Reply | Quote