Setting Purely BLANK Cell

[yokato95]

I am using an IF function that states as the following
[in Cell B1]
=IF (A1="ABC","","ELSE")

When this IF statement is TRUE, I want B1 to be a blank cell, hence the
two double quote sign without a space in between.
However, I found out that

*"COUNTBLANK (B1)" will result in 1, meaning that with COUNTBLANK
function picks B1 as a "BLANK" Cell.

*"ISBLANK (B1)" will result in FALSE, meaning that with ISBLANK
function considers B1 as a "Filled" Cell.

WHAT I WANT TO KNOW IS THE WAY TO SET B1 CELL IN A IF FUNCTION THAT IS
COMPLETELY AND PURELY BLANK SO THAT THE \"ISBLANK (B1)\" FUNCTION WILL
RESULT IN TRUE.

Does anyone know the way to do this?

The notion of Pure Blank is important when creating a pivot table,
also. By programatically setting the blank cell with "" (2 double
quotes), Pivot Table generation logic counts these as data, not blanks
thus the data count becomes inaccurate.

Thank you very much for your help in advance,

 

[Duke Carey]

So long as you have a formula in the cell it will NEVER be blank.

If it is important to have a truly blank cell, then you'll need to use VBA
code that assess whether the cells in the specified range should contain a
value or be blank.

 

[yokato95]

Thank you Duke!
Would you happen to know the VBA function to set the cell blank ?

Thanks again!

 

[Bernd Plumhoff]

Hello,

If you use LEN(B1)=0 instead of ISBLANK(B1), would that solve your problem?

Regards,
Bernd

 

[Duke Carey]

In your example you are trying to affect cell B1 based on A1's value.
Assuming you want all of column B to be affected by the contenst of A, and
that you have a consistent test, i.e., if Ax = "ABC" then Bx is blank, then
use the following code. BTW, this assumes you select the cells in column B
before running the macro:

Sub SetCellsToBlank()
dim rng as Range

for each rng in selection
if rng.offset(0,-1)="ABC" then
rng.clear
else
rng="Else"
end if
next rng

end sub

So long as you have a formula in the cell it will NEVER be blank.

If it is important to have a truly blank cell, then you'll need to use VBA
code that assess whether the cells in the specified range should contain a
value or be blank.

I am using an IF function that states as the following
[in Cell B1]
=IF (A1="ABC","","ELSE")

When this IF statement is TRUE, I want B1 to be a blank cell, hence the
two double quote sign without a space in between.
However, I found out that

*"COUNTBLANK (B1)" will result in 1, meaning that with COUNTBLANK
function picks B1 as a "BLANK" Cell.

*"ISBLANK (B1)" will result in FALSE, meaning that with ISBLANK
function considers B1 as a "Filled" Cell.

WHAT I WANT TO KNOW IS THE WAY TO SET B1 CELL IN A IF FUNCTION THAT IS
COMPLETELY AND PURELY BLANK SO THAT THE \"ISBLANK (B1)\" FUNCTION WILL
RESULT IN TRUE.

Does anyone know the way to do this?

The notion of Pure Blank is important when creating a pivot table,
also. By programatically setting the blank cell with "" (2 double
quotes), Pivot Table generation logic counts these as data, not blanks
thus the data count becomes inaccurate.

Thank you very much for your help in advance,