Guys,
I need your help to modify the following code so that Chart 2 only picks up and updates data series A (Row 14) & D (Row 17) while considering dynamic variable column ranges as usual.
Usually, the ranges I work with are consecutive such Chart 1 that references Row 7:10 in the xl file.
However, I've run into a scenario where Chart 2, Row 14 & 17 are non-consecutive and I need the code to be able to update both Charts that contain different types of ranges.
Consequently, both Charts should use the same column header in row 6 while updating their individual data series using VBA. I've attached the xl file for your review.
Thank you,
I need your help to modify the following code so that Chart 2 only picks up and updates data series A (Row 14) & D (Row 17) while considering dynamic variable column ranges as usual.
Usually, the ranges I work with are consecutive such Chart 1 that references Row 7:10 in the xl file.
However, I've run into a scenario where Chart 2, Row 14 & 17 are non-consecutive and I need the code to be able to update both Charts that contain different types of ranges.
Consequently, both Charts should use the same column header in row 6 while updating their individual data series using VBA. I've attached the xl file for your review.
Thank you,
Code:
Sub ChartTest()
Dim LastCol As Long
Dim ChtData As Range
LastCol = Sheets("DATA").Cells(6, Columns.Count).End(xlToLeft).Column
'Chart1
Worksheets("DATA").ChartObjects("Chart 1").Chart.SetSourceData _
Source:=Sheets("DATA").Range(Sheets("DATA").Cells(10, 4), Sheets("DATA").Cells(6, LastCol)), PlotBy:=xlRows
'Chart2
Set ChtData = Sheets("DATA").Range(Sheets("DATA").Cells(14, 4), Sheets("DATA").Cells(14, LastCol))
Set ChtData = Sheets("DATA").Range(Sheets("DATA").Cells(17, 4), Sheets("DATA").Cells(17, LastCol))
Set ChtData = Union(ChtData, Sheets("DATA").Cells(6, 4).Resize(1, LastCol - 4 + 1))
Worksheets("DATA").ChartObjects("Chart 2").Chart.SetSourceData _
Source:=ChtData, PlotBy:=xlRows
End Sub