How to only select chart series that are not <blank series> using VBA?

[Rnkhch]

Hello,

I have a chart of 40 data series that automatically adapt to the data points based on a slew of names that I defined, and I'm happy with it except that depending on which series appear in the data table, I would have to unselect all the <blank series> manually. This is the only aspect of the chart that is not automatic, and I wish to write some VBA code that would make that happen. But I'm a total newb at VBA, so I hope someone can help me with this.

I tried to get this done by looking online, but it seems to be a bit complex. But I think I can adapt a basic code to my chart if someone can get me the blueprint. Please let me know if I need to supply any chart info in the form of XL2BB or something.

Thanks much for any input!

[p.s.
I have some code already (that I wrote based on info I found online) in my file that helps me with automatic hide/unhide of a few sheets depending on a dropdown menu selection, so I would need to have the chart code together with this code I already have, and I'm not sure where exactly the new code, if any, would go (before or after)?]

 

[sijpie]

The following sub will remove empty series from a graph (the data of an empty serie won't show up anyway, but it will appear in the legend)
You will need a way to regenerate the graph for the next time, like have a template graph available with all the series in it, or VBA to generate the graph from scratch.

Sub RemoveEmptySeries()
    Dim srS As Series

    '' Check if chart is active
    If ActiveChart Is Nothing Then
        '' There is no active chart
        MsgBox "Please select a chart and try again.", vbExclamation, _
          "No Chart Selected"
        Exit Sub
    End If

    '' Loop through all series
    For Each srS In ActiveChart.SeriesCollection
        If IsEmpty(srS.Values(1)) Then
            srS.Delete
        End If
    Next
End Sub

 

[sijpie]

The code above is based on code written by Jon Peltier to modify a series source. The Excel Chart SERIES Formula - Peltier Tech

 

[Rnkhch]

Thank you for sharing this! It was great to go through this VBA exercise. If you find a way to deal with the legend as well, please share.

 

[sijpie]

The code should remove the series and so the legend for that series should as well disappear