Hardcoding Blank cells

[josecervmon]

Hello-

I'm looking for an excel function to hardcode a cell as blank. In particular I'm running into problems with the "If" function. For example, when I enter the following function:
=IF(3=0,"yy","&quot
(which evaluates to FALSE of course)
the "" in the argument doesn't produce a blank cell as I want it to (when I run a "ISBLANK()" on the cell where I put that formula it evaluates as "FALSE", so it doesn't take the "" as being blank). Is there something I can put in the last argument of the "if" function to get a blank cell?

Thanks

 

[DaleWatson123321]

Hello,

How about using this alternative formula...

=IF(LEN(A1)=0,TRUE,FALSE)

It will give you the same TRUE result as the ISBLANK.

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[TonyJollans]

Hi josecervmon,

If your cell has a formula in, it is not blank. HOWEVER Excel is a little bit inconsistent, so ..

If your formula is in A1 ..

=ISBLANK(A1) returns FALSE, and

=COUNTBLANK(A1) returns 1. In other words: even though it's not blank it is COUNTed as a blank.

This means that you can use:

=COUNTBLANK(A1)=1 to return TRUE.

Enjoy,
Tony

 

[josecervmon]

Thanks Dale & Tony--
Well you guys can tell I'm definitely a beginner. Neither of these quite do the trick for me, what I really want is to get an actual BLANK cell back as I have other code that will manipulate these cells based on whether they are blank or not (from what you say Tony--this is impossible as I have a formula in the cell ??).
To be specific, I'm working on a macro that deletes all rows that contain a 0 (zero) or are blank in a certain column (see thread on Deleting Rows after a keyword which I replied to with a question on 17sep03).
I know the following will delete rows that are Blank:

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Now how do I delete rows where the cell has a specific value (for instance = 0)? My first jab at this is to set cells = 0 as BLANK and then just use the line of code above, this seemed like the most elegant solution here. Would you have any other suggestions to accomplish this?

I hope I was able to explain myself here.

Thanks again

 

[TonyJollans]

Hi josecervmon,

AFAIK you can't make a cell really blank without using VBA (apart from using the <Delete> key of course).

I looked at your question in thread707-591387 and you are asking for a method without a loop, so we need to find a way of selecting a lot of non-adjacent cells. This can be done with the special cells, but the criteria you can use are limited so we need to engineer a situation where they can be used. One way to do this is by using a Filter, an Autofilter being, I think, the easiest in your case. This should do what you want:

Rows(1).Insert

 ' In case you don't have headings

Range([L1], [L65536].End(xlUp)).AutoFilter Field:=1, Criteria1:="0"
Range([L2], [L65536].End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
SElection.AutoFilter

 ' Switch off Autofilter

Rows(1).Delete

 ' Delete the row added at the beginning

If you have column headings you won't need the Insert and Delete Row statements but I included because Autofilter never filters the first row.

Enjoy,
Tony