Excel XP, Win XP
I have 14 rows of data I want to chart, each row in a different chart.
The following code charts row 1 as the "X" axis and the values in row 2 as
the "Y" axis. Good.
Sub TestChart()
Range("C1:R2").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
This code creates only one chart, as expected.
But I want to create charts of row 1 versus each of the other rows (13
charts).
I've tried a loop through all the rows using a Union of row 1 and each other
row as the range. Didn't work.
I know I can copy row 1 and each of the other rows, in turn, to a Utility
sheet, and chart that.
My question: How can I create the 13 charts without resorting to the Utility
sheet method?
Thanks for your time. Otto

Union worked just fine in this simple procedure:

Sub ChartEachRow()
Dim iRow As Long
Dim cht As Chart
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
For iRow = 2 To 14
Set rng = Union(wks.Range("C1:R1"), wks.Range("C" & iRow & ":R" & iRow))
Set cht = ActiveWorkbook.Charts.Add
cht.SetSourceData Source:=rng
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Excel XP, Win XP
> I have 14 rows of data I want to chart, each row in a different chart.
> The following code charts row 1 as the "X" axis and the values in row 2 as
> the "Y" axis. Good.
> Sub TestChart()
> Range("C1:R2").Select
> Charts.Add
> ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
> ActiveChart.Location Where:=xlLocationAsNewSheet
> End Sub
> This code creates only one chart, as expected.
> But I want to create charts of row 1 versus each of the other rows (13
> charts).
> I've tried a loop through all the rows using a Union of row 1 and each
> other row as the range. Didn't work.
> I know I can copy row 1 and each of the other rows, in turn, to a Utility
> sheet, and chart that.
> My question: How can I create the 13 charts without resorting to the
> Utility sheet method?
> Thanks for your time. Otto
>

Jon
Thanks for that. I'll try it and see. Otto
"Jon Peltier" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Union worked just fine in this simple procedure:
>
> Sub ChartEachRow()
> Dim iRow As Long
> Dim cht As Chart
> Dim rng As Range
> Dim wks As Worksheet
>
> Set wks = ActiveSheet
> For iRow = 2 To 14
> Set rng = Union(wks.Range("C1:R1"), wks.Range("C" & iRow & ":R" &
> iRow))
> Set cht = ActiveWorkbook.Charts.Add
> cht.SetSourceData Source:=rng
> Next
> End Sub
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Otto Moehrbach" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Excel XP, Win XP
>> I have 14 rows of data I want to chart, each row in a different chart.
>> The following code charts row 1 as the "X" axis and the values in row 2
>> as the "Y" axis. Good.
>> Sub TestChart()
>> Range("C1:R2").Select
>> Charts.Add
>> ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
>> ActiveChart.Location Where:=xlLocationAsNewSheet
>> End Sub
>> This code creates only one chart, as expected.
>> But I want to create charts of row 1 versus each of the other rows (13
>> charts).
>> I've tried a loop through all the rows using a Union of row 1 and each
>> other row as the range. Didn't work.
>> I know I can copy row 1 and each of the other rows, in turn, to a Utility
>> sheet, and chart that.
>> My question: How can I create the 13 charts without resorting to the
>> Utility sheet method?
>> Thanks for your time. Otto
>>
>
>