How to create and then edit a blank new hidden chart in VBA?

[Kelvin Stott]

I would like to do the following in VBA:

1. Create an invisible/virtual blank new chart, which does not appear in the workbook and does not yet contain any data (this will be added in step 2).
2. Refer to this chart as an object (so that I can add data generated by my macro, and then edit and format the chart while still invisible).
3. Display the chart with controls to interact with it in a user form, without showing it in the workbook.

Currently I'm having trouble with the first step, as my macro creates a new chart that appears as a visible chartobject in the worksheet, and contains random unwanted data (that I can't seem to get rid of) depending on what cell(s) are selected when I run my macro.

Can anybody help, please?

 

[Kelvin Stott]

Re: How to create a blank new hidden chart in VBA?

OK, I fumbled around for a couple of hours and managed to complete step 1 with the following code:

    Dim DChart As Chart, DataSeries As Series
    Set DChart = ActiveSheet.Shapes.AddChart.Chart
    With DChart
        For Each DataSeries In .SeriesCollection
            DataSeries.Delete
        Next DataSeries
        .Parent.Visible = False
    End With

This creates a new chart called DChart, then deletes the data and makes the chart invisible, just as I wanted for step 1. But before I begin step 2 and write some code to add data and edit this chart, I just wanted to ask:

Is there a simpler way (single-line command) to delete all the original data (entire SeriesCollection) in the new chart, without having to delete each individual data series in turn??? I tried SeriesCollection.Delete, but that doesn't work.