Removing dummy series from my legend

[RawlinsCross]

I have 15 series of data plotted against time on the x-axis. Some of the 15 columns contain numeric data and some contain #N/A. I have a macro that plots all 15.

If 13 of the columns contain #N/A ofcourse no data is plotted and only 2 of the columns that contain numeric data are plotted. But my problem is that the legend entries of all 15 columns show up even though only two series are plotted.

How can I write code that goes through my legend and removes those that correspond with #N/A columns?

 

[thisoldman]

I did this to delete legend entries for the series I did not want displayed:

Sub RemoveLegendEntries()
    Dim i As Long
    For i = ActiveChart.SeriesCollection.Count To 1 Step -1
       If ActiveChart.SeriesCollection(i).Name <> "bbbb" Then
            ActiveChart.Legend.LegendEntries(i).Delete
        End If
    Next i
End Sub

I found I had to step through the LegendEntries from the high index value to the low or an "Automation Error" was generated.

The only way to repopulate the legend is to first delete it entirely with 'ActiveChart.HasLegend = False' and then recreate it with 'ActiveChart.HasLegend = True'.

 

[RawlinsCross]

For each data set, I have a combo box with either "yes" or "no" added to them. When you select "yes" from anyone of the 16 data choices, it graphs those sets. The problem is that if you choose only 3 yes's it legends them all. So I'm trying to do something like this based on your example (which is not working).

The comobox's are from 20 to 35 corresponding to SeriesCollection 1 to 16.

Dim i As Long
For i = 35 To 20 Step -1
    If Me.ComboBox(i).Value = "No" Then
        ActiveChart.Legend.LegendEntries(i - 19).Delete
    End If
Next i

 

[thisoldman]

Is it not deleting any legend entries?

You could add this line to watch the progress in the immediate window as you step through the loop:

    If Me.ComboBox(i).Value = "No" Then

        Debug.Print ActiveChart.SeriesCollection(i - 19).Name

        ActiveChart.Legend.LegendEntries(i - 19).Delete
    End If

 

[RawlinsCross]

This seems to have worked.

Dim i As Long
For i = 35 To 20 Step -1
    If Me.Controls("ComboBox" & i).Value = "No" Then
    Debug.Print ActiveChart.SeriesCollection(i - 19).Name
        ActiveChart.Legend.LegendEntries(i - 19).Delete
    End If
Next I