Turning the visibility of chart series on/off using excel Macros/vba

Whenever I don't know how to do something like this, I turn on the macro recorder.

I had a chart with four series, and I used the filter function in Excel 2013 to hide and show the second series, while the macro recorder was running.

Here's the relevant code:

ActiveChart.FullSeriesCollection(2).IsFiltered = True' series 2 is now hiddenActiveChart.FullSeriesCollection(2).IsFiltered = False' series 2 is now visible

The series type (line or column) does not matter, this works for any of them.

I believe the property you are looking for is the SeriesCollection.Format.Line.Visible property. I quickly created an Excel workbook and added a simple data set (just 1-10) and added a line graph "Chart 2" to the sheet Sheet1.

This code turned the visibility of the line off:

Option ExplicitPrivate Sub Test()    Dim cht As Chart    Dim ser As Series    'Retrieve our chart and seriescollection objects'    Set cht = Worksheets("Sheet1").ChartObjects("Chart 2").Chart    Set ser = cht.SeriesCollection(1)    'Set the first series line to be hidden'    With ser.Format.Line        .Visible = msoFalse    End WithEnd Sub

And likewise, setting the ser.Format.Line.Visible property to msoTrue made the line visible again.

As for retrieving the chart itself I had to first activate it, then set my cht variable to the ActiveChart. To view the name of your chart, select it and look in the name box (near where you would enter the cell value / formula).

Update

When using the method above, the series name remains in the legend box. I couldn't find a visibility property for the SeriesCollection in the legend, however one workaround is to simply re-name the series as an empty string (this will make the series disappear from the legend) and then rename the series when you want to show it.

This code below will toggle the visibility of the line and series name in the legend.

Option ExplicitPrivate Sub Test()    Dim cht As Chart    Dim ser As Series    'Retrieve our chart and seriescollection objects'    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart    Set ser = cht.SeriesCollection(1)    'Set the first series line to be hidden'    With ser.Format.Line        If .Visible = msoTrue Then            .Visible = msoFalse            ser.Name = vbNullString        Else            .Visible = msoTrue            ser.Name = "Series 1"        End If    End WithEnd Sub

And, whenever you use .Format.Line.Visible = msoTrue just remember to set ser.Name back to whatever the name for your series is.

There is a simple way to on & off the visibility of the series: using filter on your source data.May it help you easily as follows. You can insert a new Window. Setone of them to source data sheet and the other window to Chart sheet. Then arrange the two windows to see both at the same time. Now if you filter the series you like on the source data sheet simultaneously you will see the series you desired on the other sheet.