Chart Series With Zero Values - How to ommit from chart

Hi All

Can anyone help with a problem with a chart series.

I have 12 Legend Entries (series) within a line chart. These represent temperature readings, and the data used will always have 12 records. I have used the offset function to calculate the range of data .

My problem is how to ommit the data in a column (series) if that column consist of only zeros....ie there was no temperature recorded or it was not in use.

Any ideas of how to approach the problem would be apprecated..

Kindest regards


Diane

Hi Diane (of the narrow lines),

Try replacing the ZERO VALUES with blanks. According to Andy Pope the Excel Chart Wizard '#N/A' is supposed to work, but BLANKS seem to work better for me. He has many excellent examples that have helped me many times at:http://www.andypope.info/charts/brokenlines.htm

See the attached Excel 2003 workbook containing a line chart created with the Chart Wizard containing several series. Delete the items in Column G one by one, and watch the line disappear.

Lewis

Hi Lewis

Thanks for coming to the rescue again.

It's not quite as simple as that. I have attached a sample data sheet for you to have a look at.

Diane

Test Temperature Chart.xlsx

Hi Diane,

Not so simple requires a not so simple solution.

Click on the Chart in question then run the following Macro. It should delete all Data Series with ALL ZERO values.

Lewis

Sub DeleteActiveChartSeriesWithZeroValues()

  Dim myRange As Range
  Dim rCell As Range

  Dim iDeleteCount As Integer
  Dim iFirstCharacterPos As Integer
  Dim iLastCharacterPos As Integer
  Dim iSeriesNumber As Integer
  Dim iSeriesCount As Integer
  
  Dim NeedToDeleteThisSeries As Boolean
  
  Dim x As Single
  
  Dim sFormula As String
  Dim sRange As String
  Dim sValue As String
  On Error Resume Next
  
  'Get the number of series
  'Exit if the User didn't click the Active Chart
  iSeriesCount = ActiveChart.SeriesCollection.Count
  If Err.Number <> 0 Then
    MsgBox "Click on a Chart, then try again."
    Exit Sub
  End If
  
  'Loop thru each series in the Chart (from back to front)
  'because Deleting a series pushes all other series forward
  For iSeriesNumber = iSeriesCount To 1 Step -1
  
     'Iniitialze the Delete Flag to enable deletion of this series
     NeedToDeleteThisSeries = True
  
    'Get the formula for the Current Series
    sFormula = ActiveChart.SeriesCollection(iSeriesNumber).Formula
    
    'The text at the end contains the range for the Y values
    'e.g. '...[Test Temperature Chart.xls]Sheet1'!$M$2:$M$8,10)'
    '
    'Extract the text at the end that contains the range:
    'The First character in the range is the character after the '!' (exclamation point)
    'The Last character in the range is the character before the ',' (comma)
    '
    'Remove '$' (Dollar Signs) from the range - hope they aren't Sterling signs on the other side of the pond.
    '
    iFirstCharacterPos = InStrRev(sFormula, "!") + 1
    iLastCharacterPos = InStrRev(sFormula, ",") - 1
    If iFirstCharacterPos > 0 And iFirstCharacterPos < iLastCharacterPos Then
      sRange = Mid(sFormula, iFirstCharacterPos, iLastCharacterPos - iFirstCharacterPos + 1)
      Set myRange = Range(sRange)
    End If
    
    'Loop through the Y values
    'Keep the Series if any value is NOT ZERO (ignore values that are non-numeric - should never happen)
    For Each rCell In myRange
      sValue = rCell.Text
      'Debug.Print rCell.Address(False, False) & "   " & sValue
      If IsNumeric(sValue) Then
        x = CSng(sValue)
        If x <> 0# Then
          'This series has a NON-ZERO value - it's a Keeper
          NeedToDeleteThisSeries = False
          Exit For
        End If
      End If
    Next rCell
    
    'Delete this series if all the data in it is zero
    If NeedToDeleteThisSeries = True Then
      iDeleteCount = iDeleteCount + 1
      ActiveChart.SeriesCollection(iSeriesNumber).Delete
    End If
    
  Next iSeriesNumber
    
  On Error GoTo 0
  
  MsgBox "Deletion of Data Series will All ZERO Values Completed." & vbCrLf & _
         iDeleteCount & " set(s) of data containing ALL ZERO VALUES were deleted."

End Sub

Hi Lewis

Just to let you know the code is just what I needed. It's working like a dream.

Thanks

Diane