Generating a graph using nonadjacent columns

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

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


--
HTH,
Bernie
MS Excel MVP


"John Easton" <[email protected]> wrote in message
news:[email protected]...
> 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

That got it - thanks!

John

"Bernie Deitrick" wrote:

> 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
>
>
> --
> HTH,
> Bernie
> MS Excel MVP
>
>
> "John Easton" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>