Looping through all chart series for all charts

andybluejay

New Member
Joined
Dec 22, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
VBA Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
there is no changing of sheets, so no events involved.
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top