In my experience you either have to use range names,
or build the ranges and reset them as data changes..

Coding that is a bit tedious (and not so recommended) but in a pinch it
works..

"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?


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