Selecting range for charts

[swamit]

I have 2 questions really - Once I draw a chart, how can I delete it thru the macro ? - a new one is drawn continuously and hence I need the old one to be deleted....

Second question....
I want to draw a line chart thru a macro - the data for the chart is sometimes 10 rows, sometimes 20...
Column C contains data that changes with time...
So I am using this to find out how many lines of data do I have for the chart

Range("AA1&quot.Value = "=Count(C:C) + 1"

So I get a number that tells me how many rows of data I have....say the above line returns 20...Then concatenate that with letter C...and get "C20" - and lets say I store this value in cell AA2...so when I draw my chart, I should be able to do this (look at the range object)

Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Workbooks("New.xls&quot.Sheets("Sheet1&quot.Range("C1:AA2&quot, PlotBy:=xlColumns

Now here I used AA2 in my range since my range is ever-changing....It doesnt like that...I tried this and some other things but am baffled by the intricate stuff that I did myself....
Please help !!!

 

[SkipVought]

Hi,
As far as deleting charts...
1. Specific charts either by Name or Index, ie

ActiveWorkbook.Chart(2).Delete

2. To delete all charts

    For Each Chart In ActiveWorkbook.Charts
        Chart.Delete
    Next

To create a chart pointing to a variable range, see my FAQ regarding renaming ranges. Use a named range such as "SourceData", as explained and then...

    Charts.Add
    With ActiveChart
        .ChartType = xlLineMarkersStacked
        .SetSourceData _
            Source:=[SourceData], _
            PlotBy:=xlColumns
    End With

Hope this helps Skip,
metzgsk@voughtaircraft.com