Looping through all chart series for all charts

[andybluejay]

I have a workbook with 20 Charts each of which has 24 data series. Often many of these data series are empty, so I've written code to loop through all the series in each chart and filter them depending on it there is data present or not. This works well overall for me, but one chart is a combined chart that has 48 data series. I tried to handle this case using ActiveChart.SeriesCollection.Count but that will only count series which are currently showing.

I'm sure there is a simple way to loop over all series on a chart without hardcoding the number, but I am not sure exactly how.

Sub LoopThroughCharts()

Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

For Each sht In ActiveWorkbook.worksheets
  For Each cht In sht.ChartObjects
    cht.Activate
    
    For i = 1 To 24
    x = i * 47 - 46
    If (worksheets("Data").Cells(x, 3).Value <> "") Then
        ActiveChart.FullSeriesCollection(i).IsFiltered = False
    Else
        ActiveChart.FullSeriesCollection(i).IsFiltered = True
    End If
    Next i
    
    If ActiveChart.SeriesCollection.Count > 24 Then
        For j = 25 To 48
        x = (j - 24) * 47
        If (worksheets("Data").Cells(x, 3).Value <> "") Then
            ActiveChart.FullSeriesCollection(j).IsFiltered = False
        Else
            ActiveChart.FullSeriesCollection(j).IsFiltered = True
        End If
        Next j
    End If
    
  Next cht
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

 

[BSALV]

there is no changing of sheets, so no events involved.

Sub LoopThroughCharts()
     Dim sht, i1, i2, x
     For Each sht In ActiveWorkbook.Worksheets                  'loop through sheets
          For i1 = 1 To sht.ChartObjects.Count                  'loop through indexes of chart
               With sht.ChartObjects(i1).Chart                  'that chart
                    For i2 = 1 To .FullSeriesCollection.Count   'loop through the series
                         Select Case i2                         'indexnumber serie
                              Case 1 To 24                      'first 24
                                   x = i2 * 47 - 46
                                   .FullSeriesCollection(i2).IsFiltered = (Worksheets("Data").Cells(x, 3).Value <> "")

                              Case Else                         'next
                                   x = (i2 - 24) * 47
                                   .FullSeriesCollection(i2).IsFiltered = (Worksheets("Data").Cells(x, 3).Value <> "")
                         End Select
                    Next
               End With
          Next
     Next sht
End Sub

 

[andybluejay]

Thanks! I ended up figuring it out a work around on my own but I'll have to give that a shot as it is definitely a cleaner solution.