Set Chart Source To Non Contiguous Range Variables

[ryuryubu]

Searched but could not find resolution, please help if possible.

I am trying to create a macro that will graph series or ranges that is declared. The need to declare the range comes from the fact that the source sheet is updated and grows by the day therefore I cannot write a code using fixed a data range, or else the graph won't update.

 
Dim LastRow As Integer 
Dim RNG1 As Range, RNG2 As Range, RNG3 As Range 
 
Sheets("Summary").Select 
LastRow = Range("B65536").End(xlUp).Row 
 
Set RNG1 = Range(Cells(LastRow - 90, 2), Cells(LastRow, 2)) 
Set RNG2 = Range(Cells(LastRow - 90, 10), Cells(LastRow, 10)) 
Set RNG3 = Range(Cells(LastRow - 90, 12), Cells(LastRow, 12)) 
 
Charts.Add 
ActiveChart.ChartType = xlArea 
ActiveChart.SetSourceData Source:=Range("RNG1, RNG2, RNG3"), PlotBy:=xlColumns 

I'm having problems with the last line.

The code is supposed to identify the last row and use the 90 data points leading up to the last row to create a series graph.

Thanks for your help in advance!!

[daniel.c]

Maybe you may try to use dynamic named ranges (eg. if you want to select the 90 last cells of columns B, use the following formula) :
=OFFSET(Summary!$B$1,COUNTA(Summary!$B:$B)-90,,90)
there must be no empty cells between the first used cell and the last used cell of the column.
Then you can use the named ranges into the chart in lieu of ranges.
HTH
Daniel

---

Auto Merged Post Until 24 Hrs Passes;

PS.
To correct your syntax :

ActiveChart.SetSourceData Source:=Union(RNG1, RNG2, RNG3), PlotBy:=xlColumns 

Daniel