growth in size of worksheet

[Jim]

I add and delete numerous lines to a workbook every day. Although the actual
updated visiable file should be relatively small (~35K), it keeps growing
daily apparently holding on to deleted (not visable) rows until its size is

350M (10,000 times larger) and it comes to a crawl to update. How do I keep

the size managable.

Thank you

 

[Bony Pony]

One of the things I found that can adversely affect the size of a WS is the
number of "broken" external links. It can easily happen if it is a sheet
that contains External Links and gets passed around a lot. In 2003, the Name
Manager Addon was very helpful in identifying these rogue links. Not sure
how in 2007 though ..

Also - Pivot Tables use an underlying cache and while the table itself has
low overhead, the cache can be quite expensive. If you have many tables
querying the same data, make sure they all reference the same pivotcache and
that will again reduce the size.

HTH
Regards,
Bony

 

[Dave Peterson]

I've never seen a file grow that large. And I've even tried to use a file that
large.

I would not try to correct the problem.

I would start a new workbook and copy the data, formulas, formatting from just
the ranges that I used into that new workbook.

Don't forget Names, code, page setup, objects, ....

======
Is there any chance that you've been adding objects (pictures/controls) each
time you update the data--maybe copying from web pages and having invisible
objects pasted into the worksheet?

 

[Justin Larson]

I'm having the same problem. I have a set of data that is referenced by pivot
tables in another workbook. I use the other workbook for monthly invoicing.
I typically add 15-25,000 rows each month, so I have to move some of the
source data off sheet to make room for the new data.

The idea is that the pivot tables in my workbook reference the same range
each month and all I have to do is hit refresh. However, after about 4
cycles, the document is becoming unruly (slow) and takes about 15 minutes to
refresh.

In background, the pivot tables are referenced by formulas that calculate
down to a rather complex invoice. I have to refresh the document about 50
times each month for invoicing different clients and it's gotten nearly
impossible.

My workbook is at about 90MB, and my suspicion is that the pivot cache keeps
getting bigger each time I delete and add new data to the source, instead of
refreshing and looking at only the current data.

Any way to delete, refresh, clear, or otherwise check on the pivot cache?
I've seen some comments, but nothing concrete yet and recreating the workbook
each month would defeat the purpose of having it.

-Liver