Automatic Excel caption (empty series)

Question

In my spreadsheet I have a chart that currently receives 46 series.

All series are linked to databases that depend on a single variable, so when choosing the variable some of the series return #N/D, I would need to hide the series in the caption field that does not have data. In the figure below for example only the series 19 and 22 should be in the legend.

Answer 1

I solved via VBA

ActiveSheet.ChartObjects("Gráfico 39").Activate

Dim cht    As Chart
Dim ser    As Series
Dim i, j     As String
Dim wshtA           As Worksheet
Dim EGCD   As Double

    'Formata a séries e cria o rótulos para cada série
    For i = 1 To 46
    Set ser = ActiveChart.FullSeriesCollection(i)
    If i = 1 Then
    Else
        ActiveChart.FullSeriesCollection(i).IsFiltered = False
        ActiveChart.FullSeriesCollection(i).Select
        With Selection.Format.Line
            .Visible = msoFalse
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
        End With
        Selection.MarkerSize = 15
        Selection.Format.Line.Visible = msoFalse
        ser.MarkerForegroundColor = RGB(255, 0, 0)
        ActiveChart.FullSeriesCollection(i).ApplyDataLabels
        ActiveChart.FullSeriesCollection(i).DataLabels.Select
        Selection.ShowSeriesName = True
        Selection.ShowValue = False
        Selection.Position = xlLabelPositionCenter
        ActiveChart.FullSeriesCollection(i).HasLeaderLines = False
    End If
Next i

'Filtra as séries que contém "Série Vazia" no título
For i = 1 To 46
    Set ser = ActiveChart.FullSeriesCollection(i)
    With ser.Format.Line
        If ser.Name = "Série Vazia" Then
            ser.IsFiltered = True
        Else
            ser.IsFiltered = False
        End If
    End With
Next i