Change Chart Data Source To Different Column Ranges Using VBA

[rafal1122]

Hi

I spent last day or so trying to create a macro that would update the data source of the series in the chart. I went thorugh dozens of solutions which I found on the net and none of them are doing what I need them to do.

The problem is that the series of data on the chart pull data from different columns of the data source table.

This is what I get when I update the Data Source and record the macro:

ActiveChart.SetSourceData Source:=Sheets(1).Range("L12:L65,N12:N65")

what I would like to replace it with is the following:

Dim MyDataSource1 as Range
Dim MyDataSource2 as Range


LastCellColumnL = ....
LastCellColumnN = ...
Set MyDataSource1 = ActiveSheets.Range(L12, LastCellColumnL)
Set MyDataSource2 = ActiveSheets.Range(N12, LastCellColumnN)

ActiveChart.SetSourceData Source:=Sheets(1).Range(MyDataSource1,MyDataSource2)

The problem is that why I do this, it does not treat it the same way as the original line, it also includes all the columns in between the two data ranges.
How to make

ActiveChart.SetSourceData Source:=Sheets(1).Range(MyDataSource1,MyDataSource2)

to work like:

ActiveChart.SetSourceData Source:=Sheets(1).Range("L12:L65,N12:N65")

and only include the two columns like in the original line here?

Rafal