Automate Chart

[riedyp]

Hello,
I am trying to use data that is displayed in this chart in columns R to V. However I want to only include rows that have a value displayed in my data for my Gantt chart. I am trying to figure out how to insert the correct data in vba and was wondering if I could get some help because this is beyond my knowledge at this point.

Here is my current code:

Private Sub Worksheet_Calculate()
Dim ch As ChartObject
Set ch = Worksheets("Executive Summary").ChartObjects("Chart 7")
UpdateRow = Worksheets("Executive Summary").Cells(Rows.Count, 17).End(xlUp).Row 'Only select data that is displayed
ch.Chart.Axes(xlValue).MinimumScale = [T2]
ch.Chart.Axes(xlValue).MaximumScale = [U2]
ch.Chart.SetSourceData Source:=Worksheets("Executive Summary").Range(Cells(3, 19), Cells(UpdateRow, 19))


End Sub

 

[GlennUK]

I think you need to alter both series, like this:

Dim ch As ChartObject
Set ch = Worksheets("Executive Summary").ChartObjects("Chart 7")
UpdateRow = Worksheets("Executive Summary").Cells(Rows.Count, 17).End(xlUp).Row 'Only select data that is displayed
ch.Chart.Axes(xlValue).MinimumScale = [T2]
ch.Chart.Axes(xlValue).MaximumScale = [U2]
 
ch.Chart.FullSeriesCollection(1).Formula = _
        "=SERIES('Executive Summary'!R3C20,'Executive Summary'!R4C19:R" & UpdateRow & "C19,'Executive Summary'!R4C20:R" & UpdateRow & "C20,1)"
ch.Chart.FullSeriesCollection(2).Formula = _
        "=SERIES('Executive Summary'!R3C22,'Executive Summary'!R4C19:R" & UpdateRow & "C19,'Executive Summary'!R4C22:R" & UpdateRow & "C22,2)"