How to delete cells having formula with blank output?

**Damongz** · 01-10-2011, 12:43 PM

My worksheet has some cells containing formula, but the output of the formula in these cells are blanks. How can I delete them with macros? I tried this one:

Sheets("Output").Select
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)

But the macros said no blank cells was found. Any advice is welcome. Thanks alot.

**DonkeyOte** · 01-10-2011, 03:30 PM

A Blank is a cell devoid of all content (including formulae)
Nulls are Text Strings (0 length)

Without knowing what else resides on your sheet you might consider using

SpecialCells(xlCellTypeFormulas,xlTextValues)

and iterating resulting range checking for Nulls.
(alternatively you might be able to delete en masse - we simply don't know what is feasible at this stage given we have no knowledge of other data types etc)

**Damongz** · 01-11-2011, 11:32 AM

Thanks for your reply.
In my worksheet, all the cells are results of formulas. But based on condition, some cells will have blank output. I want to remove the cells having "blank" output.
And I tried your suggested code. It changed all the cells with output of text values.

**DonkeyOte** · 01-11-2011, 12:17 PM

Originally Posted by Damongz

I tried your suggested code. It changed all the cells with output of text values.

I think I made the point that pending other values you would use the SpecialCells to identify possible locations:

Originally Posted by D.O

...iterating resulting range checking for Nulls.

You need to check the value of each formula text string to see if it's a Null or not.

Hopefully you can appreciate that based on what you've told us we are effectively having to resort to "shooting in the dark"