How to not chart empty series

[travismorien]

I am trying to build dynamic charts which chart only the series with
non-zero data in them.

i.e. I've got the chart pointed to a data range of say 20 different
series, of which only a handful are likely to have non-zero values in
them at any given moment.

How do I chart JUST the ones with non-zero values, and hide or ignore
the ones which are just a row of zeros?

I don't want to delete those data series permanently, just not chart
them.

Travis

 

[Earl Kiosterud]

Travis,

Try Tools - Options - Chart - Zero values: Not plotted. These apply to
individual data points, not entire series, but you don't say what kind of
chart you're using, so I'm not sure if it applies.

DOn't ask why these chart properties are in Tools - Options. No one knows.
Don't ask why they're not instead in the Chart menu option, and in the
context (right-click) menu for a chart. No one knows. If there's more than
one chart, they apply only to the selected chart. No one knows.

 

[Jim Cone]

Have your formulas return #N/A instead of zero.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<[email protected]>
wrote in message
I am trying to build dynamic charts which chart only the series with
non-zero data in them.

i.e. I've got the chart pointed to a data range of say 20 different
series, of which only a handful are likely to have non-zero values in
them at any given moment.
How do I chart JUST the ones with non-zero values, and hide or ignore
the ones which are just a row of zeros?
I don't want to delete those data series permanently, just not chart
them.
Travis

 

[travismorien]

I came up with a VBA solution for this problem, but I'm getting a
runtime error on the very last line of the function. (Object variable
or With block variable not set)

It doesn't like this line; "RemoveZeroSeries = chartable.Address"

Everything else works fine. The message boxes which come up show
exactly what they are supposed to show, so the only error really is on
that one line.

Can anyone see what is wrong with the very last line of my function?

Code follows:

Private Sub RemoveEmptyChartSeries_Click()

Range("IncomeChartNominalNoZeros").Name =
RemoveZeroSeries(Range("IncomeChartNominalData"))


ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SetSourceData Source:=Sheets("Projection
1").Range("IncomeChartNominalNoZeros"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Range("ProjectionYears")

End Sub

Private Function RemoveZeroSeries(inputrange As Range) As Range

Dim DownCounter, AcrossCounter, NumberOfRows As Integer

Dim temprange, chartable, upperleft As Range

' delete the next two lines for the function
'Dim inputrange As Range
'Set inputrange = Range("IncomeChartNominal")


MsgBox "There are " & inputrange.Rows.Count & " data series"

NumberOfRows = inputrange.Rows.Count

Set upperleft = inputrange.Resize(1, 1)

MsgBox "The Upper Left Cell is at " & upperleft.Address


' Find the first series which isn't all zeros, and name its range
"chartable"

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 1 To (Range("YearsProjection").Value)

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0 Then

Set chartable = Range(upperleft.Offset(DownCounter, 0).Address &
":" & upperleft.Offset(DownCounter,
Range("YearsProjection").Value).Address)


GoTo GotFirstChartRangeSoBreakOutOfLoop

End If

Next AcrossCounter

Next DownCounter

GotFirstChartRangeSoBreakOutOfLoop:

' Now build up the rest of the range by adding additional ranges which
also have non zeros

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 1 To (Range("YearsProjection").Value)

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0 Then

Set temprange = Range(upperleft.Offset(DownCounter, 0).Address &
":" & upperleft.Offset(DownCounter,
Range("YearsProjection").Value).Address)



Set chartable = Union(chartable, temprange)

AcrossCounter = 1
GoTo ThisSeriesHasAtLeastOneNonZeroSoMoveOnToTheNextOne

End If

Next AcrossCounter

ThisSeriesHasAtLeastOneNonZeroSoMoveOnToTheNextOne:

Next DownCounter

MsgBox "The range that will be charted is " & chartable.Address
RemoveZeroSeries = chartable.Address

End Function

 

[travismorien]

I came up with a VBA solution for this problem, but I'm getting a
runtime error on the very last line of the function. (Object variable
or With block variable not set)

Never mind replying, I fixed the function by defining it as a String
instead of a range.

Travis

 

[travismorien]

Travis,

Try Tools - Options - Chart - Zero values: Not plotted. These apply to
individual data points, not entire series, but you don't say what kind of
chart you're using, so I'm not sure if it applies.

Entire series!

DOn't ask why these chart properties are in Tools - Options. No one knows.
Don't ask why they're not instead in the Chart menu option, and in the
context (right-click) menu for a chart. No one knows. If there's more than
one chart, they apply only to the selected chart. No one knows.

Travis