I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!

Also, I only have one option in "Look in", which is formula. Don't know how
to find back "value"?

€œjwang036€�编写：

I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!

try this

the cell has formula - use copy | paste special | values | ok and then
use replace function.

or

go to tools | options | view tab | uncheck zero values | ok


On Oct 23, 2:39 pm, jwang036
wrote:
Also, I only have one option in "Look in", which is formula. Don't know how
to find back "value"?

"jwang036"±àд£º



I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!- Hide quoted text -

- Show quoted text -

Have you tried Autofilter to filter out anything not a "0" and clearing
contents of those cells?

Would have to be run on each column.

Or run a macro.

Sub zero_gone()
For Each cell In ActiveSheet.UsedRange
If cell.Value = 0 Then
cell.Value = ""
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Thu, 23 Oct 2008 02:27:01 -0700, jwang036
wrote:

I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!

They could have been good ideas. Unfortunately, I have to keep all the
formulas and the Excel is in fact linked to Access, even I turn off "0" view
in Excel, access will still show all the zeros.
Thanks any way!

€œmuddan madhu€�编写：

try this

the cell has formula - use copy | paste special | values | ok and then
use replace function.

or

go to tools | options | view tab | uncheck zero values | ok


On Oct 23, 2:39 pm, jwang036
wrote:
Also, I only have one option in "Look in", which is formula. Don't know how
to find back "value"?

"jwang036"编写：



I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!- Hide quoted text -

- Show quoted text -

The code works well. Thanks!

So far I only have 500 rows x 15 columns. It can grow very fast, say tens of
thousands of rows. Will checking cell by cell become very slow?

€œGord Dibben€�编写：

Have you tried Autofilter to filter out anything not a "0" and clearing
contents of those cells?

Would have to be run on each column.

Or run a macro.

Sub zero_gone()
For Each cell In ActiveSheet.UsedRange
If cell.Value = 0 Then
cell.Value = ""
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Thu, 23 Oct 2008 02:27:01 -0700, jwang036
wrote:

I wrote formulas something like =if(a1=0,0,1) all over a worksheet. Now I
want to empty all the cells that show "0". I used "Replace" function, but it
replaces the "0" in my formula, which causes errors.

By the way, for some complicated reasons I can't use =if(a1="","",1). I have
to find which cells should be "0", and then clear them. Thanks!