Size of shared workbook growing dramatically without significant activity

[wiseguy298]

Our client services department has a shared revenue spreadsheet that contains multiple tabs. The file has grown from 3mb to more than 18mb without any significant activity... Opening the file is taking longer and longer... Any thoughts?

Thanks
Jeff

---------- Post added at 11:57 AM ---------- Previous post was at 11:55 AM ----------

Realized I should have included additional information... Windows 7 and Office 2010

[Richard Buttrey]

Whoa, whoa whoa.

The words 'shared workbook' fill me with dread. You are where you are I know but the general consensus is avoid these like the plague, for all sorts of reasons that I won't go into here. I'd hazard a guess that someone has inadvertently screwed this up.

So the first thing to do is take it off line, switch sharing off and examine every sheet for the last used cell. In my experience the most common cause of expanding workbooks is someone copying a formula or formulae down to the last row 1048576, or at least a significant number of rows. If the formula evaluates to blank you won't necessarily see any result which is why they may not be obvious. So do and End Home on each sheet (or switch on display formulae) to see if this has happened.

The next thing I usually do is to iteratively delete half the sheets noting the ones you've deleted, save a copy and check if the file is back to a more reasonable size. If not open the saved copy, delete half of the sheets again, save and check again. Keep repeating this process until you find a sheet which seems to be the cause of the problem. Then repeat the process but iteratively deleting half the rows and checking the file size. Eventually you should pinpoint the cause of the problem.

The other thing you could try is looking for embedded shape objects. Only the other week someone asked me the same question. Their filesize was huge but with only a few sheets and rows/columns it was a puzzle since it should have been just a few Kb. When I typed the Instruction
ActiveSheet.Shapes.Count it returned over 1100! On further investigation there were 1100 rectangle drawing objects with no width or height which were invisible. When I deleted all these with a looping macro the file size was back to normal.

Hope this helps - and consider other methods than sharing a workbook if you want to consolidate or summarise data from many users.

[K.₿ ~ Knew Money]

Solved a similar issue here in a new thread titled: Dramatically speed up VBA SLOWED by loading large reference database workbook