Conditionally Filter chart categories according to the x-axis labels

Hi all.

I have a workbook with chart that often need to have series or categories removed, depending on the current situation.
It was very easy to set cell formulas to return "N/A" when the series or category is not needed.
Similarly it was easy to read the series name and filter series objects accordingly.

But I can not work out how to relate the category labels to the category objects.
If I loop through the categories, I have no way to read the categories labels.
If I loop through the x-axis labels (from the axes object or the seriesCollection), I have no way to refer back to Category object.

Can anyone suggest a way to accomplish the filtering of the category object?

Here is my code so far:

Code:

---

Private Sub FilterChart(cht As Chart)    On Error GoTo eh
   
    Dim S As series
    Dim catLabel As String
    'filter Series
    For Each S In cht.SeriesCollection
        If S.Name = "#N/A" Or S.Name = "NA" Or S.Name = "N/A" Then
            S.IsFiltered = True
        End If
    Next S


    Dim i As Long
   
    'method to loop though the x-axis labels
    Dim catName As String
    For Each catName In cht.Axes(xlCategory).CategoryNames
        'note this has no reference to the category object
        Debug.Print catName
        If catName = "#N/A" Or catName = "NA" Or catName = "N/A" Then
            'how do I find which category object this relates to?
            '*********Needed******
            'SomeRefToCategory.IsFiltered = True
        End If
    Next
   
    'OR  method to loop through categories
    Dim i As Long
    Dim ChtGrp As ChartGroup
    Dim fullCatColl As CategoryCollection

    For Each ChtGrp In cht.ChartGroups
        Set fullCatColl = ChtGrp.FullCategoryCollection
        For i = fullCatColl.Count To 1 Step -1
            'Note I have no way to get the name of the category
            'how do I know which categories to filter?
            'If some measure
                'fullCatColl(i).IsFiltered = True
            'end if
        Next i
    Next ChtGrp
   
    Exit Sub


eh:
    #If vardebug = 1 Then
        Debug.Print Err.Number, Err.Description
        Debug.Assert False
        Resume
    #End If
End Sub


Private Sub UnfilterChart(cht As Chart)
    Dim S As series
    For Each S In cht.FullSeriesCollection
        S.IsFiltered = False
    Next
   
    Dim grp As ChartGroup
    Dim i As Long
    For Each grp In cht.ChartGroups
        For i = 1 To grp.FullCategoryCollection.Count
            grp.FullCategoryCollection(i).IsFiltered = False
        Next i
    Next grp
End Sub

---

Oringinally post at
https://www.excelforum.com/excel-pro...ollection.html

for use on microsoft 365 on windows 10 and office for mac 365 (High Sierra and up)