Hiding Data points Equal to Zero

[R Tanner]

Anyone have any ideas what is wrong with this code?

Sub modifyseries()
Dim cht As Chart
Dim srs(1 To 4) As series

Set cht = ActiveSheet.ChartObjects("Chart 5").Chart
Set srs(1) = Sheets(3).ChartObjects("Chart
5").Chart.SeriesCollection(1)
Set srs(2) = Sheets(3).ChartObjects("Chart
5").Chart.SeriesCollection(2)
Set srs(3) = Sheets(3).ChartObjects("Chart
5").Chart.SeriesCollection(3)
Set srs(4) = Sheets(3).ChartObjects("Chart
5").Chart.SeriesCollection(4)

If cht.SeriesCollection(1).XValues <> 0 Then
srs(1).Points.Visible = False
End If

End Sub

I want to hide a data point if it equals 0. Thanks..

 

[RyanH]

Try this change in your code,

If cht.SeriesCollection(1).XValues = 0 Then
srs(1).Points.Visible = False
End If

 

[R Tanner]

Try this change in your code,

If cht.SeriesCollection(1).XValues = 0 Then
srs(1).Points.Visible = False
End If

Okay Thanks Ryan. I will give it a try.

 

[R Tanner]

Okay Thanks Ryan. I will give it a try.

Its still tellin me it's a mismatch...I swear I'm not retarded, but I
can't see what's wrong with it...

 

[Peter T]

srs(1).Points.Visible = False

That won't work as the Points collection does not have a Visible property,
neither does an individual Point. Even if it did work it would hide all
points in the entire series, rather than an individual zero data point as
you originally asked.

To hide an entire series or individual points is a matter of formatting
border and, depending on the series chart-type, possibly markers or interior
as None or no-fill as appropriate. Select an individual point and record a
macro while applying the formats. Also record a macro with an entire series
selected, reformat with all the default automatic settings. So in any loop
start by making all visible then hiding individual points as needed.

A loop might be something like this (aircode)

dim i as Long, v as Variant
Dim sr as Series

for each sr in ActiveChart.SeriesCollection
' reset default formats to make all visible
sr.border.linestyle = xlAutomatic
for i = 1 to Ubound(sr.Values) ' or sr.points.count
v = sr.Values(i)
' cater for #N/A
if isError(v) then v = 1 ' or maybe 0
if v = 0 then
with sr.Points(i)
' format to make, border.Weight & markers or interior xlNone
'etc

I haven't bothered with the code, partly as it will depend on your chart
type, but I wonder if you really need to hide your zero value points. With a
bar type with and (typically) the X axis on Y = 0 zero bars won't be
visible. Also look at Tools / Options / Chart and the various display
options particularly with line types and maybe replace zeros with empty or
#N/A.

Regards,
Peter T