Consulting

Results 1 to 1 of 1

Thread: Conditionally Filter chart categories according to the x-axis labels

  1. #1
    VBAX Regular
    Joined
    Mar 2021
    Posts
    6
    Location

    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:
    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)
    Last edited by truk; 06-14-2021 at 01:49 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •