replacing zeros by blank cells

  • Thread starter Thread starter milet
  • Start date Start date
M

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,
 
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
 
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? ...
Click to expand...

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.
 
Try this small macro:

Sub dural2()
For Each r In Selection
If r.Value = 0 Then
r.Clear
End If
Next
End Sub
 
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.
 
Back
Top