replacing zeros by blank cells


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,
 
Ad

Advertisements

B

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

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.
 
G

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
 
Ad

Advertisements

M

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top