A recent post to the Microsoft Excel Charting Discussion Group asks . . . I have a series of embedded charts in a workbook. Some contain data, some do not. How can I automatically print only those charts with data? The following macro loops through each embedded chart in the workbook. For each embedded chart, it loops through each series recording the point values for those series. After looping through all of the series, if the total point values are not equal to zero, the chart prints. If the total point values are equal to zero, the macro proceeds to the next chart without printing.

Sub PrintChartsWithValues()

    Application.ScreenUpdating = False

    Dim hSheet As Worksheet
    Dim Ws As Worksheet
    Dim Cht As ChartObject
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long
    Dim iPt As Long

    Set hSheet = ActiveSheet

    For Each Ws In Worksheets
        For Each Cht In Ws.ChartObjects
            Y = 0
            For Each Srs In Cht.Chart.SeriesCollection
                With Srs
                    nPts = .Points.Count
                    For iPt = 1 To nPts
                        X = Srs.Values(iPt)
                        Y = X + Y
                    Next
                End With
            Next Srs
            If Y <> 0 Then Cht.Chart.PrintOut Copies:=1
        Next Cht
    Next Ws

    hSheet.Select
    Range("A1").Select

End Sub

John -

A good start. Here are some defensive programming steps you can take to make the procedure more bulletproof. (It’s so much harder to program for other people than for yourself.)

1. Use Option Explicit, declare X and Y as Double, and hSheet as Object (since the active sheet may be a chart sheet).

2. You should use

X = Abs(Srs.Values(iPt))

so you don’t ignore, for example, a symmetric sine wave.

3. If Srs.Values(iPt) contains an error value, then X = Srs.Values(iPt) throws a Type Mismatch error. The X = and Y = lines should be enclosed in a construct of

If Not IsError(Srs.Values(iPt)) Then
  ....
End If

4. In a line or XY chart, if the values of a series consist entirely of nonplotting data (blank cells or #N/A), then

nPts = .Points.Count

raises a run time error. You will have to trap this:

On Error Resume Next
nPts = .Points.Count
If Err.Number = 0 Then
For iPt = 1 To nPts
....
Next
End If
On Error GoTo 0

5. There’s a question in my mind which category of “Some [charts] contain data, some do not” should include charts with numerical zeros as their data. In my experience, I would generally consider a chart with zero values to have data, so I would follow a different approach, parse the series formula for the (X and) Y values, and check whether they contain numerical data, or no data (text, errors, and blanks). I ignored the question because I didn’t feel like parsing a series formula at the time.

Jon,

Thanks for all of the suggestions.  I admittedly provided an answer that wasn’t well tested and didn’t think of adding the error traps, declaring all of the variables, etc.  These are all good coding practices.

I understand all items with the exception of item #2.  By using ABS wouldn’t you possibly miss negative values on the X-Axis?

Thanks again.

John Mansfield

ABS won’t make you miss negative values, it prevents negative values from canceling out positive values. If I had three points, with values -1, 0, and +1, if I summed the values I’d come up with 0, and the algorithm would indicate that the series has no values. But if I sum ABS values, I’d come up with +2, which obviously shows the presence of values.