Hi,

I have an Excel file that reads data from an external source and creates an
X-Y chart from the data. Each time the data is read, the number of rows is
different and I need to delete the old data. The chart is created in such a
way that each row is a new series.

In order to delete the old data I have simple loop that goes like this:
For i = 4 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
Next i
I want to keep the first three series intact all the time.

The problem I have is that if for whatever reason there are any <empty
series> in the chart, where the chart has a series pointing to cell that have
been cleared, the Delete method fails and I cannot delete the series using
VBA. I have to delete them manually.

Is there a way I can circumvent this and be able to delete the series with
VBA anyway?

Thanks for your help!!

Fermon
P.S. The real simple answer is to delete the series before I update the
chart, but my users need to have access to the data in the chart and they
could delete some cells to run different scenarios. So this is still a
problem for me...

If the data is properly arranged, you could use SetSourceData and change the
entire data range. This wouldn't hose any series that still have proper
data; it adds new series if the range expands, and removes series if the
range contracts.

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


"Fermon" <(E-Mail Removed)> wrote in message
news:20A40BFB-E7D5-4C4B-9358-(E-Mail Removed)...
> Hi,
>
> I have an Excel file that reads data from an external source and creates
> an
> X-Y chart from the data. Each time the data is read, the number of rows is
> different and I need to delete the old data. The chart is created in such
> a
> way that each row is a new series.
>
> In order to delete the old data I have simple loop that goes like this:
> For i = 4 To ActiveChart.SeriesCollection.Count
> ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
> Next i
> I want to keep the first three series intact all the time.
>
> The problem I have is that if for whatever reason there are any <empty
> series> in the chart, where the chart has a series pointing to cell that
> have
> been cleared, the Delete method fails and I cannot delete the series using
> VBA. I have to delete them manually.
>
> Is there a way I can circumvent this and be able to delete the series with
> VBA anyway?
>
> Thanks for your help!!
>
> Fermon
> P.S. The real simple answer is to delete the series before I update the
> chart, but my users need to have access to the data in the chart and they
> could delete some cells to run different scenarios. So this is still a
> problem for me...

Jon,

Thanks for your response. I looked into your suggestion but it does not seem
to work well for this particular case. It could be that I do not know how to
make it work. I'll give you more details to explain why. Let's say the data
is arranged in three columns:

SNames xVals yVals
S1 .1 10
S2 .11 10
S3 .1 20
S4 .12 15
S5 ...

The x-y chart is organized so that each row is actually one series. I create
the series with VBA because otherwise it would be incredibly tedious:each
series is created and the series name is "S#", the X value is the number in
the second column and the Y value is the third column. I want to leave the
first three series intact all the time (they are in fact calculated from the
other data) while S4 and so on are recreated each time there is new data.

If I use SetSourceData, I'll have to recreate the chart, including the first
three series because the range is too complex. Perhaps there is a way to pass
on the range, but I could not figure it out. When I use the chart wizard to
try to get hints from Excel it says that the Chart Data Range is too complex
to be displayed.

I guess I could use SetSourceData as a safe way to eliminate all the series
each time and then recreate all series, including the first three, with code.
I was wondering, though, if there was a way to delete those series with
values that have been cleared. The wizard allows me to delete them but I
can't do that with VBA so far.

Please let me know if you need additional information to picture the problem.

Thanks for your help,

Fermon

"Jon Peltier" wrote:

> If the data is properly arranged, you could use SetSourceData and change the
> entire data range. This wouldn't hose any series that still have proper
> data; it adds new series if the range expands, and removes series if the
> range contracts.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Fermon" <(E-Mail Removed)> wrote in message
> news:20A40BFB-E7D5-4C4B-9358-(E-Mail Removed)...
> > Hi,
> >
> > I have an Excel file that reads data from an external source and creates
> > an
> > X-Y chart from the data. Each time the data is read, the number of rows is
> > different and I need to delete the old data. The chart is created in such
> > a
> > way that each row is a new series.
> >
> > In order to delete the old data I have simple loop that goes like this:
> > For i = 4 To ActiveChart.SeriesCollection.Count
> > ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
> > Next i
> > I want to keep the first three series intact all the time.
> >
> > The problem I have is that if for whatever reason there are any <empty
> > series> in the chart, where the chart has a series pointing to cell that
> > have
> > been cleared, the Delete method fails and I cannot delete the series using
> > VBA. I have to delete them manually.
> >
> > Is there a way I can circumvent this and be able to delete the series with
> > VBA anyway?
> >
> > Thanks for your help!!
> >
> > Fermon
> > P.S. The real simple answer is to delete the series before I update the
> > chart, but my users need to have access to the data in the chart and they
> > could delete some cells to run different scenarios. So this is still a
> > problem for me...
>
>
>

Well, I did say "If the data is properly arranged...." <g>

Why do you need separate one-point series? If you just need separate labels
on each point, use one of these utilities:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

Back to your issue, if the data is not plottable (i.e., it's blank), you
cannot access certain properties and methods of a series, if it is a
marker-type series (Line or XY). However, you can convert it to a different
type first:

With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
.ChartType = xlColumnClustered
.Delete
End With

When the series is re-added, it reverts to the main chart type, XY.

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


"Fermon" <(E-Mail Removed)> wrote in message
news:05C119B5-FAFC-438D-BED6-(E-Mail Removed)...
> Jon,
>
> Thanks for your response. I looked into your suggestion but it does not
> seem
> to work well for this particular case. It could be that I do not know how
> to
> make it work. I'll give you more details to explain why. Let's say the
> data
> is arranged in three columns:
>
> SNames xVals yVals
> S1 .1 10
> S2 .11 10
> S3 .1 20
> S4 .12 15
> S5 ...
>
> The x-y chart is organized so that each row is actually one series. I
> create
> the series with VBA because otherwise it would be incredibly tedious:each
> series is created and the series name is "S#", the X value is the number
> in
> the second column and the Y value is the third column. I want to leave the
> first three series intact all the time (they are in fact calculated from
> the
> other data) while S4 and so on are recreated each time there is new data.
>
> If I use SetSourceData, I'll have to recreate the chart, including the
> first
> three series because the range is too complex. Perhaps there is a way to
> pass
> on the range, but I could not figure it out. When I use the chart wizard
> to
> try to get hints from Excel it says that the Chart Data Range is too
> complex
> to be displayed.
>
> I guess I could use SetSourceData as a safe way to eliminate all the
> series
> each time and then recreate all series, including the first three, with
> code.
> I was wondering, though, if there was a way to delete those series with
> values that have been cleared. The wizard allows me to delete them but I
> can't do that with VBA so far.
>
> Please let me know if you need additional information to picture the
> problem.
>
> Thanks for your help,
>
> Fermon
>
> "Jon Peltier" wrote:
>
>> If the data is properly arranged, you could use SetSourceData and change
>> the
>> entire data range. This wouldn't hose any series that still have proper
>> data; it adds new series if the range expands, and removes series if the
>> range contracts.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Fermon" <(E-Mail Removed)> wrote in message
>> news:20A40BFB-E7D5-4C4B-9358-(E-Mail Removed)...
>> > Hi,
>> >
>> > I have an Excel file that reads data from an external source and
>> > creates
>> > an
>> > X-Y chart from the data. Each time the data is read, the number of rows
>> > is
>> > different and I need to delete the old data. The chart is created in
>> > such
>> > a
>> > way that each row is a new series.
>> >
>> > In order to delete the old data I have simple loop that goes like this:
>> > For i = 4 To ActiveChart.SeriesCollection.Count
>> >
>> > ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
>> > Next i
>> > I want to keep the first three series intact all the time.
>> >
>> > The problem I have is that if for whatever reason there are any <empty
>> > series> in the chart, where the chart has a series pointing to cell
>> > that
>> > have
>> > been cleared, the Delete method fails and I cannot delete the series
>> > using
>> > VBA. I have to delete them manually.
>> >
>> > Is there a way I can circumvent this and be able to delete the series
>> > with
>> > VBA anyway?
>> >
>> > Thanks for your help!!
>> >
>> > Fermon
>> > P.S. The real simple answer is to delete the series before I update the
>> > chart, but my users need to have access to the data in the chart and
>> > they
>> > could delete some cells to run different scenarios. So this is still a
>> > problem for me...
>>
>>
>>

Jon,

Thanks for your help on this. Yes you did warn me about the data
arrangement, and you were right about the labeling. I need to have separate
labels for each point, but I need them to appear in the ScreenTip, when the
user hovers over a specific point.

The charts quickly become unreadable when there are too many labels and the
screentip allows me to show the extra specific data I need for the point
without cluttering the entire graph. Not quite in the format I'd like but the
information is there.

I downloaded Rob Bovey's chart labeler and, in fact, I had experimented with
labels myself when I first started to create this spreadsheet. The problem is
that labels do not work well for me. Now, if there is a similar utility that
allows me to change the ScreenTip, I would really like to get that one. The
problem, though is that I am still haaving to create individual series for
each data point with the limitations that the approach imposes.

Perhaps I can use mapping techniques to follow the user's mouse over the
chart and provide extra info when appropriate but that sounds like a lot more
work than creating separate series for each row. Also I am fortunate enough
that the 255 (or less) Excel series limitation is not a major roadblock for
this particular application.

The technique you gave me for changing the Chart type and then deleting the
series works for me. I'll incorporate that into my application.

Thanks again,

Fermon

"Jon Peltier" wrote:

> Well, I did say "If the data is properly arranged...." <g>
>
> Why do you need separate one-point series? If you just need separate labels
> on each point, use one of these utilities:
>
> Rob Bovey's Chart Labeler, http://appspro.com
> John Walkenbach's Chart Tools, http://j-walk.com
>
> Back to your issue, if the data is not plottable (i.e., it's blank), you
> cannot access certain properties and methods of a series, if it is a
> marker-type series (Line or XY). However, you can convert it to a different
> type first:
>
> With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
> .ChartType = xlColumnClustered
> .Delete
> End With
>
> When the series is re-added, it reverts to the main chart type, XY.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______

Jon,

After responding to your post, I thought I'd look again for "screentips" or
"screen tips" and, what do you know, I found a reference to an article
written by you on the very subject I need. The article is here:
<http://www.computorcompanion.com/LPMArticle.asp?ID=221> in case someone else
does a search on the topic.

I will look into this, using the MouseMove event to see if I get anywhere.
Any updated info you may have or books you recommend are appreciated.

Thanks so much again,

Fermon

"Fermon" wrote:

> Jon,
>
> Thanks for your help on this. Yes you did warn me about the data
> arrangement, and you were right about the labeling. I need to have separate
> labels for each point, but I need them to appear in the ScreenTip, when the
> user hovers over a specific point.
>
> The charts quickly become unreadable when there are too many labels and the
> screentip allows me to show the extra specific data I need for the point
> without cluttering the entire graph. Not quite in the format I'd like but the
> information is there.
>
> I downloaded Rob Bovey's chart labeler and, in fact, I had experimented with
> labels myself when I first started to create this spreadsheet. The problem is
> that labels do not work well for me. Now, if there is a similar utility that
> allows me to change the ScreenTip, I would really like to get that one. The
> problem, though is that I am still haaving to create individual series for
> each data point with the limitations that the approach imposes.
>
> Perhaps I can use mapping techniques to follow the user's mouse over the
> chart and provide extra info when appropriate but that sounds like a lot more
> work than creating separate series for each row. Also I am fortunate enough
> that the 255 (or less) Excel series limitation is not a major roadblock for
> this particular application.
>
> The technique you gave me for changing the Chart type and then deleting the
> series works for me. I'll incorporate that into my application.
>
> Thanks again,
>
> Fermon
>
> "Jon Peltier" wrote:
>
> > Well, I did say "If the data is properly arranged...." <g>
> >
> > Why do you need separate one-point series? If you just need separate labels
> > on each point, use one of these utilities:
> >
> > Rob Bovey's Chart Labeler, http://appspro.com
> > John Walkenbach's Chart Tools, http://j-walk.com
> >
> > Back to your issue, if the data is not plottable (i.e., it's blank), you
> > cannot access certain properties and methods of a series, if it is a
> > marker-type series (Line or XY). However, you can convert it to a different
> > type first:
> >
> > With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
> > .ChartType = xlColumnClustered
> > .Delete
> > End With
> >
> > When the series is re-added, it reverts to the main chart type, XY.
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______
>

Tushar Mehta (http://tushar-mehta.com) has a utility called Chart Hover
Label which provides custom pseudo chart tips.

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


"Fermon" <(E-Mail Removed)> wrote in message
news:1228B40A-66EB-4494-AD5B-(E-Mail Removed)...
> Jon,
>
> After responding to your post, I thought I'd look again for "screentips"
> or
> "screen tips" and, what do you know, I found a reference to an article
> written by you on the very subject I need. The article is here:
> <http://www.computorcompanion.com/LPMArticle.asp?ID=221> in case someone
> else
> does a search on the topic.
>
> I will look into this, using the MouseMove event to see if I get anywhere.
> Any updated info you may have or books you recommend are appreciated.
>
> Thanks so much again,
>
> Fermon
>
> "Fermon" wrote:
>
>> Jon,
>>
>> Thanks for your help on this. Yes you did warn me about the data
>> arrangement, and you were right about the labeling. I need to have
>> separate
>> labels for each point, but I need them to appear in the ScreenTip, when
>> the
>> user hovers over a specific point.
>>
>> The charts quickly become unreadable when there are too many labels and
>> the
>> screentip allows me to show the extra specific data I need for the point
>> without cluttering the entire graph. Not quite in the format I'd like but
>> the
>> information is there.
>>
>> I downloaded Rob Bovey's chart labeler and, in fact, I had experimented
>> with
>> labels myself when I first started to create this spreadsheet. The
>> problem is
>> that labels do not work well for me. Now, if there is a similar utility
>> that
>> allows me to change the ScreenTip, I would really like to get that one.
>> The
>> problem, though is that I am still haaving to create individual series
>> for
>> each data point with the limitations that the approach imposes.
>>
>> Perhaps I can use mapping techniques to follow the user's mouse over the
>> chart and provide extra info when appropriate but that sounds like a lot
>> more
>> work than creating separate series for each row. Also I am fortunate
>> enough
>> that the 255 (or less) Excel series limitation is not a major roadblock
>> for
>> this particular application.
>>
>> The technique you gave me for changing the Chart type and then deleting
>> the
>> series works for me. I'll incorporate that into my application.
>>
>> Thanks again,
>>
>> Fermon
>>
>> "Jon Peltier" wrote:
>>
>> > Well, I did say "If the data is properly arranged...." <g>
>> >
>> > Why do you need separate one-point series? If you just need separate
>> > labels
>> > on each point, use one of these utilities:
>> >
>> > Rob Bovey's Chart Labeler, http://appspro.com
>> > John Walkenbach's Chart Tools, http://j-walk.com
>> >
>> > Back to your issue, if the data is not plottable (i.e., it's blank),
>> > you
>> > cannot access certain properties and methods of a series, if it is a
>> > marker-type series (Line or XY). However, you can convert it to a
>> > different
>> > type first:
>> >
>> > With
>> > ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
>> > .ChartType = xlColumnClustered
>> > .Delete
>> > End With
>> >
>> > When the series is re-added, it reverts to the main chart type, XY.
>> >
>> > - Jon
>> > -------
>> > Jon Peltier, Microsoft Excel MVP
>> > Tutorials and Custom Solutions
>> > Peltier Technical Services, Inc. - http://PeltierTech.com
>> > _______
>>