Hiding Data points Equal to Zero
 

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

Hiding Data points Equal to Zero
 

Try this change in your code,

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


--
Cheers,
Ryan


"R Tanner" wrote:

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

Hiding Data points Equal to Zero
 

On Jun 25, 10:11 am, RyanH wrote:
Try this change in your code,

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

--
Cheers,
Ryan

"R Tanner" wrote:
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..

Okay Thanks Ryan. I will give it a try.

Hiding Data points Equal to Zero
 

On Jun 25, 11:38 am, R Tanner wrote:
On Jun 25, 10:11 am, RyanH wrote:



Try this change in your code,

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

--
Cheers,
Ryan

"R Tanner" wrote:
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..

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

Hiding Data points Equal to Zero
 

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



"R Tanner" wrote in message
...
On Jun 25, 10:11 am, RyanH wrote:
Try this change in your code,

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

--
Cheers,
Ryan

"R Tanner" wrote:
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..

Okay Thanks Ryan. I will give it a try.