Remove #N/A from Chart Legend

[bobsell]

Hi guys,

I have a table with a large series of data on it and I have it linked to a graph. The problem is that some of the data can be #N/A. I have intentionally made it this way so that these values do not appear on the chart itself. Also, I use check boxes to hide/display data on request. The objective is to have a completely automated graph that updates itself with the selected check boxes.

If the data series name is unchecked, it becomes #N/A as well. It gets removed from the graph but not from the legend. I wrote this script but it gives me:

Runtime Error 438

Object does not support property or method

This is my code:

Sub Deleter()

Dim j As Integer
Dim i As Integer
j = ActiveChart.Legend.LegendEntries.Count
MsgBox i
MsgBox j
ActiveChart.Legend.Select
With ActiveChart
    For i = 1 To j
        .Legend.LegendEntries(i).Select
        If .Legend.LegendEntries.Name = "#N/A" Then _
            Selection.Delete
    Next
    On Error Resume Next
    
End With
End Sub

PLEASE HELP!!!!

 

[VoG]

I'm not sure that the correct property is Name (more like Text or Value I think) but I think it should be more like

Sub Deleter()

Dim j As Integer
Dim i As Integer
j = ActiveChart.Legend.LegendEntries.Count
MsgBox i
MsgBox j
ActiveChart.Legend.Select
With ActiveChart
    For i = j To 1 Step -1
        If .Legend.LegendEntries(i).Name = "#N/A" Then _
            .Legend.LegendEntries(i).Delete
    Next

End With
End Sub

 

[bobsell]

Thanks a lot for the suggestions!!

I went through some MSDN pages to get some insight about what may have caused it. I found that the LegendEntries(i).Name or .Value or .Text does not return anything, that is why I get the error. (Or so I think)

So I changed the script a bit:

Sub Deleter()

Dim j As Integer
Dim i As Integer
j = ActiveChart.Legend.LegendEntries.Count
MsgBox i
MsgBox j

With ActiveChart
    For i = 1 To j
            With ActiveChart.SeriesCollection(i)
                dName = ActiveChart.SeriesCollection(i).Name
                MsgBox dName
                
                If dName = "#N/A" Then
                    ActiveChart.Legend.LegendEntries(i).Select
                    Selection.Delete
                End If
                
            End With
    Next
    On Error Resume Next
End With
End Sub

Now dName does return a value of #N/A but it erases all other values that are not #N/A as well, and gives an error:

Run-time error -2147467259 (80004005)

Method LegendEntries of object Legend failed

Any insight?

 

[VoG]

You will certainly need to loop backwards as I showed

For i = j to 1 Step -1

 

[bobsell]

Yes you are completely right, I missed that.

I added it now, the script runs perfectly!!!!
Here is the working version:

Sub Deleter()

Dim j As Integer
Dim i As Integer
Dim q As Integer

j = ActiveChart.Legend.LegendEntries.Count

MsgBox j

With ActiveChart
    For i = j To 1 Step -1
            With ActiveChart.SeriesCollection(i)

                dName = ActiveChart.SeriesCollection(i).Name
                
                MsgBox ".Name = " & dName & vbNewLine _
                        & " & i = " & i & vbNewLine

                
                If dName = "#N/A" Then
                    ActiveChart.Legend.LegendEntries(i).Select
                    Selection.Delete
                End If

            End With
    Next
    On Error Resume Next
End With

Thanks a lot mate. In my country we say that you are Golden when someone is grateful

 

[VoG]

Great. Thanks for the feedback.