VBA add / remove Chart's data series based on cell value

[Akbarov]

Hello community,

I have student names in P2:Y2 ( in some cases they are blank in some cases they have names )
And in P3:Y10 are their scores

I have bar chart , I want to make it dynamically add / remove Data Series.
So if P2 is blank , Chart will remove P3:P10 from Data series.
And if I type student name it will add it to Chart, is it possible?

Thank you.

 

[Anthony47]

With this few information I should suggest that you recreate somewhere else your table and you plot your graph based on this second table.

To create the new table use this formula, for example in P13

=IF(P$2="",NA(),IF(P2="",NA(),P2))

Then copy to the right up to Y13; then copy the first row down in P14:P21

In this way, if a header is missing the whole serie will be #NA!, that will be ignored in the graph

Try...

 

[Domenic]

@Anthony47 has given you a great non-VBA approach. So here's an approach that uses VBA.

The following code uses the worksheet change event handler, and so it needs to be place in the code module for the sheet containing the source data. Therefore, right-click the sheet tab, select View Code, and copy/paste the code into the code module.

Note that the code assumes that the chart is located in the same sheet as the source data, and that the chart is called "Chart 1". If this is not the case, you'll need to amend the code accordingly. For example, if the chart is located in a sheet called "Sheet2", and the chart is called "Chart 2", replace...

Set chrt = Me.ChartObjects("Chart 1").Chart

with

Set chrt = ThisWorkbook.Worksheets("Sheet2").ChartObjects("Chart 2").Chart

Here's the code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    'if more than one cell is updated at one time, exit the sub
    If Target.CountLarge > 1 Then Exit Sub
  
    'if updated cell is located outside the header row, exit the sub
    If Application.Intersect(Target, Range("P2:Y2")) Is Nothing Then Exit Sub
  
    Dim chrt As Chart
    Set chrt = Me.ChartObjects("Chart 1").Chart 'change the chart name accordingly
  
    'delete collection of series, if any
    With chrt
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
    End With
  
    Dim headerRange As Range
    Set headerRange = Range("P2:Y2")
  
    Dim sourceRange As Range
    Set sourceRange = Range("P3:Y10")
  
    Dim headerIndex As Long
    Dim srs As Series
    With headerRange
        For headerIndex = 1 To .Columns.Count
            If Len(.Cells(headerIndex)) > 0 Then
                Set srs = chrt.SeriesCollection.NewSeries
                srs.Name = "=" & .Cells(headerIndex).Address(external:=True)
                srs.Values = sourceRange.Columns(headerIndex)
            End If
        Next headerIndex
    End With

End Sub

Hope this helps!