Again something different (and not nice !!!) with 2007 (it's amazing how much
is necessary to have your all code doing the same thing as with previous
versions) :

Lets take a sheet with column A empty. add text in the first 5 rows of
column B. Select A1. Then do

ActiveWorkbook.Charts.Add

This will add a chartsheet with an ugly series that you never asked for.

Do the same with the previous versions. You will have an empty chart,
waiting for your instructions to add series.

MrT

Okay, duplicated. It seems Excel 2007 looks for a range not only containing
the active cell, but also just touching it, when guessing what range to use.
Prior versions only used a range that the active cell was part of.

I always clear out the chart of series at the beginning anyway, or
explicitly set a data range, because you can never be sure what cell or
range is selected when the code runs:

Do Until ActiveChart.SeriesCollection = 0
ActiveChart.SeriesCollection(1).Delete
Loop

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"MrT" <(E-Mail Removed)> wrote in message
news:4AF12DD7-3D96-4F74-8115-(E-Mail Removed)...
> Again something different (and not nice !!!) with 2007 (it's amazing how
> much
> is necessary to have your all code doing the same thing as with previous
> versions) :
>
> Lets take a sheet with column A empty. add text in the first 5 rows of
> column B. Select A1. Then do
>
> ActiveWorkbook.Charts.Add
>
> This will add a chartsheet with an ugly series that you never asked for.
>
> Do the same with the previous versions. You will have an empty chart,
> waiting for your instructions to add series.
>
> MrT
>

The Do-Loop works well to get rid of unwanted series when you add a chart.

However, I have found that, if you then add a new series and there is data
in more than one column, the chart SOMETIMES ends up with more than the one
series you tried to add. It depends on where the data is in the worksheet
and if it is hidden - very strange and disappointing. None of this was an
issue on 2000 thru XP.

My workaround for this is to:
1) Activate an empty worksheet before adding a chart. This way the chart
will not "see" any data that it can grab hold of and will behave when you add
a new series. You also do not need the DO-LOOP workaround; you end up with
an empty chart.

2) Add the series for your chart

3) Relocate the chart to the desired worksheet after adding all the series
you want

4) Delete the empty worksheet
--
MKlaus


"Jon Peltier" wrote:

> Okay, duplicated. It seems Excel 2007 looks for a range not only containing
> the active cell, but also just touching it, when guessing what range to use.
> Prior versions only used a range that the active cell was part of.
>
> I always clear out the chart of series at the beginning anyway, or
> explicitly set a data range, because you can never be sure what cell or
> range is selected when the code runs:
>
> Do Until ActiveChart.SeriesCollection = 0
> ActiveChart.SeriesCollection(1).Delete
> Loop
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "MrT" <(E-Mail Removed)> wrote in message
> news:4AF12DD7-3D96-4F74-8115-(E-Mail Removed)...
> > Again something different (and not nice !!!) with 2007 (it's amazing how
> > much
> > is necessary to have your all code doing the same thing as with previous
> > versions) :
> >
> > Lets take a sheet with column A empty. add text in the first 5 rows of
> > column B. Select A1. Then do
> >
> > ActiveWorkbook.Charts.Add
> >
> > This will add a chartsheet with an ugly series that you never asked for.
> >
> > Do the same with the previous versions. You will have an empty chart,
> > waiting for your instructions to add series.
> >
> > MrT
> >
>
>
>