skip zeros for chart range

[mshbhwn98]

I am trying to make a chart that alters to whatever range is given to it. Currently I execute a stored proc which populates a table then I plot the table on a graph. But when some of the values are 0 I do not want it to display the result as it just makes the chart look messy. I have managed to define the x and y ranges I believe using the following code


Private Sub DefineChartRange()
Dim xRng As Range, yRng As Range
Dim i As Long
For i = 14 To 33
If LCase(Cells(i, "D").Value) <> 0 Then
If xRng Is Nothing Then
Set xRng = Cells(i, "F")
Set yRng = Cells(i, "T")
Else
Set xRng = Union(xRng, Cells(i, "F"))
Set yRng = Union(yRng, Cells(i, "T"))
End If
End If
Next i

End Sub


But I don't know how to assign the data to the plot range. Any help would be appreciated.

Thanks

[p45cal]

something along these lines?:
Private Sub DefineChartRange()
Dim xRng As Range, yRng As Range
Dim i As Long
For i = 14 To 33
If LCase(Cells(i, "D").Value) <> 0 Then
If xRng Is Nothing Then Set xRng = Cells(i, "F") Else Set xRng = Union(xRng, Cells(i, "F"))
End If
Next i
If Not xRng Is Nothing Then
Set yRng = xRng.Offset(, 14)
With ActiveSheet.ChartObjects("Chart 1").Chart
.SeriesCollection(1).XValues = xRng
.SeriesCollection(1).Values = yRng
End With
End If
End Sub

[jolivanes]

With Code or formula, replace the zeros with #N/A or =NA()