replacing zeros by blank cells

[milet]

Hi,
Charts read cells with 0 as value, but a blank cell as a missing value so
that it is not presented on the chart. What is the trick to replace the zeros
in a large table by blank cells?
Thanks for your help,

 

[Bernard Liengme]

Blank cells are ignored by chart engine ONLY if you have specified this in
Options
To make zeros into blanks would seem to need another column
=if(B1=0,"",b1)
then plot this new column
best wsihes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

REMEMBER: Microsoft is closing the newsgroups; We will all meet again at
http://answers.microsoft.com/en-us/office/default.aspx#tab=4

 

[Héctor Miguel]

hi, !

Charts read cells with 0 as value, but a blank cell as a missing value so that it is not presented on the chart.
What is the trick to replace the zeros in a large table by blank cells? ...

constant values ?... try a macro with the following instruction:

Range("b2:b30").Replace What:="0", Replacement:="", LookAt:=xlWhole

perhaps a UDF ?... see this thread:

http://www.ozgrid.com/forum/showthread.php?t=27240

other approach...
- select "the range"
- edit > find (0, match/whole, lookin values, click "find all", press <ctrl>+A, close the dialog)
- press {del}

hth,
hector.

 

[milet]

Hector, thanks a lot. This is a good solution. Cheers,

 

[Gary''s Student]

Try this small macro:

Sub dural2()
For Each r In Selection
If r.Value = 0 Then
r.Clear
End If
Next
End Sub

 

[milet]

Bernard,
thanks, I tried this solution also, but the chart reads the "" as a zero as
well, although the zero value disappears it does not become a really blank
cell.