Data source Range and Charts

[mozart]

I have column with 20 rows of data that I have currently charted

What I want to do is get the chart to chart any additional rows of data
without going into the data source and manually extending the range.

For example if I add a 21st row I want this automatically to be
reflected in my chart.

How can this be done?

Thanks

 

[duane]

The way I get around this is with a macro where I define the range a
"chartdata" like below where this range starts in cell A1. The hav
the macro build the chart the way YOU want.

Range("A1").CurrentRegion.Name = "chartdata"
Range("chartdata").Sort Key1:=Range("B1"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Charts.Add
ActiveChart.ChartType = xlColumnClustered
With ActiveChart
.SetSourceData Source:=Sheets("chartdata").Range("chartdata"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Drug Ratings"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text
"Drugs"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text
"Ratings"
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End Wit

 

[Gord Dibben]

Mozart

What you need is a named range that grows dynamically.

See Tushar Mehta's site for info.

No need for macros, just named ranges.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Gord Dibben Excel MVP