How do I make a cell truly empty using an "If" statement?
 

If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

Hi ALHR,

With VBA, try,

Range("A1").ClearContents

If, however, your intention is to work
from Excel , it is not possible for a
formula to '"empty" a cell; at most,
a formula can make the cell seem empty.


---
Regards.
Norman
"ALHR" wrote in message
...
If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

Try ActiveCell.Clear

"ALHR" wrote:

If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

Thanks for your response. To be a little more clear, what I am trying to do
is assign a group of cells a value if a certain condition is true, and leave
them empty if false. What I have done in the past is used "" in my "if"
statement for the false condition. The problem is that while the cell
appears to be empty, Excel treats it as though it contains text.


"Norman Jones" wrote:

Hi ALHR,

With VBA, try,

Range("A1").ClearContents

If, however, your intention is to work
from Excel , it is not possible for a
formula to '"empty" a cell; at most,
a formula can make the cell seem empty.


---
Regards.
Norman
"ALHR" wrote in message
...
If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

I think that if you are going to use formulas to test the cells, then the
best approach would be to test for null strings instead of ISEMPTY.

If ActiveCell < "" Then

Or

If ActiveCell = "" Then

Because if the cell is empty, then Excel will treat it like "". But that
would not solve the CountA dilemma.

"ALHR" wrote:

Thanks for your response. To be a little more clear, what I am trying to do
is assign a group of cells a value if a certain condition is true, and leave
them empty if false. What I have done in the past is used "" in my "if"
statement for the false condition. The problem is that while the cell
appears to be empty, Excel treats it as though it contains text.


"Norman Jones" wrote:

Hi ALHR,

With VBA, try,

Range("A1").ClearContents

If, however, your intention is to work
from Excel , it is not possible for a
formula to '"empty" a cell; at most,
a formula can make the cell seem empty.


---
Regards.
Norman
"ALHR" wrote in message
...
If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

What about copying a contents of an empty cell to the one you would
like to clear?
For this purpose assign some cell that you will always keep clear.

It's an idea only. I'm not too much advanced guy in Exel.

Regards,
Greg.

How do I make a cell truly empty using an "If" statement?
 

Thanks for your response. Yeah, the probem is with the CountA. Also, when I
chart the data, Excel treats "" as 0, so it actually puts points on the chart
as Y=0 rather than just excluding them. It does this even when I select
"Plot empty cells" - "not plotted," indicating again that it is not treating
these as empty cells. Oddly, as you noted, the ISEMPTY function does treat
them as empty.

"JLGWhiz" wrote:

I think that if you are going to use formulas to test the cells, then the
best approach would be to test for null strings instead of ISEMPTY.

If ActiveCell < "" Then

Or

If ActiveCell = "" Then

Because if the cell is empty, then Excel will treat it like "". But that
would not solve the CountA dilemma.

"ALHR" wrote:

Thanks for your response. To be a little more clear, what I am trying to do
is assign a group of cells a value if a certain condition is true, and leave
them empty if false. What I have done in the past is used "" in my "if"
statement for the false condition. The problem is that while the cell
appears to be empty, Excel treats it as though it contains text.


"Norman Jones" wrote:

Hi ALHR,

With VBA, try,

Range("A1").ClearContents

If, however, your intention is to work
from Excel , it is not possible for a
formula to '"empty" a cell; at most,
a formula can make the cell seem empty.


---
Regards.
Norman
"ALHR" wrote in message
...
If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you

How do I make a cell truly empty using an "If" statement?
 

You can try using na() instead of "" in your if statement.

i.e.:

if(condition,na(), false)

instead of

if(condition, "", false)

This won't make the cell appear empty, BUT it will allow you to make a plot
without these values showing up!


"ALHR" wrote:

Thanks for your response. Yeah, the probem is with the CountA. Also, when I
chart the data, Excel treats "" as 0, so it actually puts points on the chart
as Y=0 rather than just excluding them. It does this even when I select
"Plot empty cells" - "not plotted," indicating again that it is not treating
these as empty cells. Oddly, as you noted, the ISEMPTY function does treat
them as empty.

"JLGWhiz" wrote:

I think that if you are going to use formulas to test the cells, then the
best approach would be to test for null strings instead of ISEMPTY.

If ActiveCell < "" Then

Or

If ActiveCell = "" Then

Because if the cell is empty, then Excel will treat it like "". But that
would not solve the CountA dilemma.

"ALHR" wrote:

Thanks for your response. To be a little more clear, what I am trying to do
is assign a group of cells a value if a certain condition is true, and leave
them empty if false. What I have done in the past is used "" in my "if"
statement for the false condition. The problem is that while the cell
appears to be empty, Excel treats it as though it contains text.


"Norman Jones" wrote:

Hi ALHR,

With VBA, try,

Range("A1").ClearContents

If, however, your intention is to work
from Excel , it is not possible for a
formula to '"empty" a cell; at most,
a formula can make the cell seem empty.


---
Regards.
Norman
"ALHR" wrote in message
...
If the condition is false, I want the cell to be empty. When I use ""
counta() and other functions treat the cell as text.

Thank you