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

Akbarov

Active Member
Joined
Jun 30, 2018
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,996
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Excel Formula:
=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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,523
Office Version
  1. 365
Platform
  1. Windows
@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...

VBA Code:
Set chrt = Me.ChartObjects("Chart 1").Chart

with

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

Here's the code...

VBA 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!
 

Forum statistics

Threads
1,176,103
Messages
5,901,392
Members
434,890
Latest member
creativimama

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
Top