Auto selecting the number of rows!
 

Hi all!

I was trying to plot values for 2 variables as a XY scatter plot i
Excel, using Macros and recorded a macro while performing the abov
task.
The following is the macro I recorded.

Sub le( )

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Comparison of LE ti
distance"). _
Range("L6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Comparison of LE tip distance'!R2C4:R102C4"
ActiveChart.SeriesCollection(1).Values = _
"='Comparison of LE tip distance'!R2C2:R102C2"
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"Comparison of LE tip distance"

End Sub


However, in this case since I manually selected the require
rows/columns for the graph, we can see that the cells ranging fro
!R2C4:R102C4 were selected etc....
This might not be the general case. Hence I counted the no: of activ
rows and stored it into a variable called 'i'. I was wondering if ther
would be a way to select the cells like !R2C4:R"i"C4 instead o
!R2C4:R102C4, so that the code can be generalized.

I would appreciate your suggestions guys.
Thanks alot.

Arun.
Vtec Corp

--
Message posted from http://www.ExcelForum.com

Auto selecting the number of rows!
 

Sub le( )

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Comparison of LE tip
distance"). _
Range("L6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Comparison of LE tip distance'!R2C4:R" & i & "C4"
ActiveChart.SeriesCollection(1).Values = _
"='Comparison of LE tip distance'!R2C2:R" & i & "C2"
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"Comparison of LE tip distance"

End Sub

--
Regards,
Tom Ogilvy

"aiyer " wrote in message
...
Hi all!

I was trying to plot values for 2 variables as a XY scatter plot in
Excel, using Macros and recorded a macro while performing the above
task.
The following is the macro I recorded.

Sub le( )

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Comparison of LE tip
distance"). _
Range("L6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Comparison of LE tip distance'!R2C4:R102C4"
ActiveChart.SeriesCollection(1).Values = _
"='Comparison of LE tip distance'!R2C2:R102C2"
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"Comparison of LE tip distance"

End Sub


However, in this case since I manually selected the required
rows/columns for the graph, we can see that the cells ranging from
!R2C4:R102C4 were selected etc....
This might not be the general case. Hence I counted the no: of active
rows and stored it into a variable called 'i'. I was wondering if there
would be a way to select the cells like !R2C4:R"i"C4 instead of
!R2C4:R102C4, so that the code can be generalized.

I would appreciate your suggestions guys.
Thanks alot.

Arun.
Vtec Corp.


---
Message posted from http://www.ExcelForum.com/