CODEWITHSUNDEEP
excelvbacharts
Ale
Ale

Reputation: 81

How to programatically hide/remove categories in charts?

I have a stacked column graph and i want to hide/show some of the categories on certain conditions. All solutions, i've found, work for series, but in need for categories.

Thank you in advance.

Upvotes: 1

Views: 1088

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

I recorded a macro while I filtered my chart to hide category 2, and here's what the recorder gave me:

ActiveChart.ChartGroups(1).FullCategoryCollection(2).IsFiltered = True

Upvotes: 2

Ale
Ale

Reputation: 81

I found a workaround. However, maybe somebody has a more elegant solution, it would be much appreciated.

1st i swap series and categories. 

chartSheet.ChartObjects("chart").Chart.PlotBy = xlColumns

2nd then i check which column is hidden and save an index for FullSeriesCollection. With a little convoluted way to get the sheet name and column address, where the data is located.

    Dim i As Long, k As Long
    Dim tmp() As Variant
    Dim sh As String, col As String
    For i = 1 To Sheet2.ChartObjects("tst").Chart.SeriesCollection.Count
        If Worksheets(Split(Split(Sheet2.ChartObjects("tst").Chart.SeriesCollection(i).Formula, ",")(2), "!")(0)) _
        .Range(Split(Split(Sheet2.ChartObjects("tst").Chart.SeriesCollection(i).Formula, ",")(2), ":")(1)).EntireColumn.Hidden = True Then
            k = k + 1
            ReDim Preserve tmp(1 To k)
            tmp(k) = i
        End If
    Next i

3rd after that i run through all the hidden columns and hide the corresponding data. I couldn't combine 2nd and 3rd, because if any other column, then the last one, is hidden, vba gives an error. Since it tries to access SeriesCollection, which does not exits anymore.

For i = 1 To UBound(tmp)
        chartSheet.ChartObjects("chart").Chart.FullSeriesCollection(tmp(i)).IsFiltered = True
Next i

4th and lastly i flip series and categories back around.

chartSheet.ChartObjects("chart").Chart.PlotBy = xlRows

Upvotes: 0

Related Questions