Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Empty a cell if the values equal to "IN" , "MC" or "PP" | Excel Programming | |||
delete rows if cell in row contains "a" or "o" or empty | Excel Programming | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
how can I make an excel cell "mark" or "unmark" when clicked on? | Excel Discussion (Misc queries) | |||
How can I make cell A1 a "Y" or "N" depending upon cell A2's font color? Please help. | Excel Programming |