Generating a graph using nonadjacent columns

[Guest]

Hello -

I am trying to write a macro that will automatically generate a series of
graphs. I have declared a range variable for each column of data, where the
"A" column is time, my independent variable, and the later columns are
dependent variables.

I am using the ActiveChart.SetSourceData Source:=Range(RangeVariable1,
RangeVariable2) method of defining my data. The problem is when
RangeVariable2 is the data in the "C" column, my graph includes a line for
"B" vs "A" as well as "C" vs "A", and I only want "C" vs "A" plotted. How do
I tell Excel to NOT include the columns between these nonadjacent columns?

(I tried just recording a macro while I did this manually - that code used
the absolute cell references. This doesn't help because I don't know ahead
of time the length of the data).

Any help will be appreciated.

Thanks - John

 

[Bernie Deitrick]

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(.Range("A1"), .Range("A65536").End(xlUp)), _
.Range(.Range("C1"), .Range("C65536").End(xlUp)))
ActiveChart.Location Where:=xlLocationAsObject, Name:=.Name
End With

 

[Guest]

That got it - thanks!

John

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
With Worksheets("Sheet1")
ActiveChart.SetSourceData Source:=Union(.Range(.Range("A1"), .Range("A65536").End(xlUp)), _
.Range(.Range("C1"), .Range("C65536").End(xlUp)))
ActiveChart.Location Where:=xlLocationAsObject, Name:=.Name
End With