How to delete the regression generated chart in vba?

**diablo2man** · 09-03-2010, 02:36 PM

Hi,

I'm now developing a spreadsheet that load data from outside automatically every day and generate regression report for the data. I'm now using function like

Call regress(ActiveSheet.Range("$N$2:$N$2298"), ActiveSheet.Range("$O$2:$O$2298"),False, True, , ActiveSheet.Range("$A$1"), False, False, False, True, , False)

to generate the regression report. Although it will overwrite the data area, the old chart would be still there.

Is there anyway I can avoid generate chart and update it automatically or delete it and generate a new one?

The main problem is the chart ID seems like to be random and I have more than one chart in the sheet.

Thanks!

**Andy Pope** · 09-04-2010, 07:44 AM

You would need code to remove existing charts. Some how you need to be able to distingish between those chart you want to keep and those that can be delete.
This could be their name or position or chart type.

Once the regression has run and produced charts you could use code to give those a unique name, which next time you can test for and delete.

**diablo2man** · 09-07-2010, 10:45 AM

Originally Posted by Andy Pope

You would need code to remove existing charts. Some how you need to be able to distingish between those chart you want to keep and those that can be delete.
This could be their name or position or chart type.

Once the regression has run and produced charts you could use code to give those a unique name, which next time you can test for and delete.

Thanks for the help. Could you provide the example to give chart unique name after regression has run?

**Andy Pope** · 09-08-2010, 06:50 AM

Untested

    dim lngCount as long
    dim lngIndex as long
    dim lngItem as long 
    const PREFIX ="MyRegChart"

    ' remove charts
    for lngIndex = activesheet.chartobjects.count to 1 step -1
        if left(activesheet.chartobjects(lngindex).name ,len(PREFIX)) = PREFIX then
            activesheet.chartobjects(lngindex).delete
        end if
    next

    ' get number before regression
    lngCount = Activesheet.chartobjects.count

    ' code to do regression
    ' <CODE HERE>

    for lngIndex = lngcount+1 to activesheet.chartobjects.count
        lngItem = lngItem + 1
        activesheet.chartobjects(lngindex).name = prefix & lngitem
    next

End sub