Range Variable ?
 

"Girish" wrote:

Hey Guys!

Below is a part of my code to generate charts. I want a dynamic (not fixed)
range.
for example, In "=Calculations!R27C9:R34C9", I don't want to fix my data
range. How do I go about it?

I have made my ranges dynamic when I know the starting location of a data
series, but I don't know the length of the data series. I do this by 1)
knowing the cell where the data starts at (2) Finding the length of the data,
and 3) Grabbing the address of the the data series range, and assigning that
to the chart series:

Range("R27C9", Selection.End(xlDown)).Select 'Find data range (blank cell
must be at end of data range)
temp = Selection.Address 'store the address of the data range as string in
temp
..
..
..
ActiveChart.SeriesCollection(1).XValues = temp




Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Girish"
ActiveChart.SetSourceData
Source:=Sheets("Calculations").Range("J27:L34"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(1).Values = "=Calculations!R27C10:R34C10"
ActiveChart.SeriesCollection(1).Name = "=""Count"""
ActiveChart.SeriesCollection(2).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(2).Values = "=Calculations!R27C11:R34C11"
ActiveChart.SeriesCollection(2).Name = "=""Individual"""
ActiveChart.SeriesCollection(3).XValues = "=Calculations!R27C9:R34C9"
ActiveChart.SeriesCollection(3).Name = "=""Cumulative"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Calculations"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
End With