Conditionally Filter chart categories according to the x-axis labels

[truk]

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:

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-programming-vba-macros/1351945-process-chart-by-looping-through-chart-categorycollection.html

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