Excel size increase through charts

[xyfix]

I have a excel sheet on which I place 5 charts, through vb code. Every
time a button is pressed, the code removes all charts on the sheet and
creates new ones with new data. Although the number of charts stay the
same and nothing is added extra to the sheet, it increases everytime I
press the button. It looks to me like the charts are not removed, but
stuck on the background ( not visible). If I look at the properties of
the new charts, the chart number doesn't start with number 1 as I
expect it to do.

 

[Jon Peltier]

What increases? File size?

The chart names assigned by Excel continue to increase. There's a kind of
memory of previously used shape numbers. This in itself does not indicate a
problem.

Are you deleting the chart objects on the sheet, or merely deleting the rows
or columns under the charts? If you delete the ranges, the charts will
remain, but scrunched into the top or side edge of the deleted range. You
can't see them because they are aligned with cell boundaries. You should do
something like

ActiveSheet.ChartObjects.Delete

at the beginning of your code.

- Jon

 

[Peter T]

Are you sure your code deletes old charts. Eg, deleting rows/cols they are
contained in doesn't delete them, just makes them miniscule to the extent of
making them invisible.

Charts excluding their data don't occupy much file size, just the properties
of the chart.

If I look at the properties of
the new charts, the chart number doesn't start with number 1 as I
expect it to do.

That's normal and of it's own would not impact on file size, the object
counter is only reset if all objects of any kind are removed from the sheet
then save - close - re-open.

Regards,
Peter T

 

[Peter T]

I didn't see Jon's earlier reply before posting similar comments.

Peter T

 

[Jon Peltier]

When you see it twice, it gives you some assurance of its validity.

- Jon

 

[Peter T]

Though I guess the OP hasn't seen it at all !

Regards,
Peter T

 

[xyfix]

First of all, thank you ( Jon and Peter) for replying,

I was a little bit busy the last few days so I couldn't reply, but the
solution Jon gave on his first reply is OK and it works. After
executing the line code things got cleaned up and my sheet is now 455
kb instead of the 2.8 Mb. So thanks alot you guys.

xyfix