Updating Charts and Ranges Using VBA with Non-Consecutive Ranges

[5150]

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,

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

 

[Debraj]

Hello..

First .. in your way.. so that you can get it easily..

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))
'check your 1st selection.. its whole range.. try only a single row range..

Set ChtData = Union(ChtData, Sheets("DATA").Range(Sheets("DATA").Cells(14, 4), Sheets("DATA").Cells(14, LastCol)).Offset(3))

'resize is to change the size of range.. try offset.. to jump to another range

Worksheets("DATA").ChartObjects("Chart 2").Chart.SetSourceData _
Source:=ChtData, PlotBy:=xlRows

End Sub

 

[5150]

Thank you. I also needed the Horizontal Axis Labels in Chart 2 to use the header row data in Row 6. I believe the union part of the original code was what allowed both charts to use the same Header Row in Row 6. Would you please add this line of code. The code looks pretty good so far we just need that one last piece added it to make this project work properly. I appreciate your help.

 

[Debraj]

Hello..

check this one..

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))
'check your 1st selection.. its whole range.. try only a single row range..
Set ChtData = Union(ChtData, Sheets("DATA").Range(Sheets("DATA").Cells(14, 4), Sheets("DATA").Cells(14, LastCol)).Offset(3))

'resize is to change the size of range.. try offset.. to jump to another range
With Worksheets("DATA").ChartObjects("Chart 2").Chart
  .SetSourceData Source:=ChtData, PlotBy:=xlRows
  .FullSeriesCollection(1).XValues = "=DATA!$E$6:$H$6"
End With

End Sub

Check the last section.. for header..

 

[5150]

Thank you for updating the code. Unfortunately, now the code doesn't work now with the added line of code and it doesn't look like it would expand or contract the header ranges for both Charts in Row 6 dynamically.

.FullSeriesCollection(1).XValues = "=DATA!$E$6:$H$6"

The code needs to make all the ranges expand or contract dynamically in accordance to how many data columns there are. For instance, if new data is added in column headers 5, headers 6, header 7 etc, the code should automatically expand all the ranges to the next column for Charts 1 and Charts 2. Both Charts need to utilize the same Header in Row 6 and a similar code variable like "LastCo"l that was defined to accomplish this initially should be use. Thanks again for your assistance on this new request, we're almost there.

 

[Debraj]

I dont know.. why you are making 1st chart's serier HARD CODED, and 2nd want DYNAMIC...
still lets try ...

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))
'check your 1st selection.. its whole range.. try only a single row range..
Set ChtData = Union(ChtData, Sheets("DATA").Range(Sheets("DATA").Cells(14, 4), Sheets("DATA").Cells(14, LastCol)).Offset(3))

'resize is to change the size of range.. try offset.. to jump to another range
With Worksheets("DATA").ChartObjects("Chart 2").Chart
  .SetSourceData Source:=ChtData, PlotBy:=xlRows
  .FullSeriesCollection(1).XValues = Worksheets("DATA").ChartObjects("Chart 1").Chart.FullSeriesCollection(1).XValues
End With

End Sub

 

[5150]

Unfortunately, the update code you provided still does not work properly. Both Chart's data series are to be dynamic. None of them are to be hard coded. Both of them will use the same header in row 6 and both charts using the same header should expand dynamically on all of their row series and headers series. I hope this helps clarify the objective.

 

[Debraj]

Mr.. 5150..

Can you please provide the logic behind selecting A & D for chart 2.. how you decide that these 2 rows are dynamic..

or can you elaborate lil bit more..