[RESOLVED] Excel Workbook Bloat - cloning

[VBAhack]

I'm hoping someone has a reasonably robust workbook clone macro they are willing to share. Maybe clone isn't the right word to use since I don't want an exact copy - everything minus the bloat!

I've had recent cases of annoying workbook bloat - the worst offender was a 1MB workbook that suddenly become 10MB for no apparent reason. I never could figure out what the problem was. My workbooks aren't too complex - 6 to 10 worksheets, each of which has maybe 30 columns and 100 rows, 10% to 30% blank cells, 3-4 buttons for initiating macros, 4-6 charts, 10-12 array formulas.

I'm now toying with the idea of creating a workbook clone macro. I hesitate to do simple copy/paste for fear that whatever was wrong with the original will be copied to the new. I made a crude prototype that captures 15 formatting attributes plus formula of all non-blank cells in the used range of the source workbook and re-creates in a new workbook. The 10MB workbook was copied and resulted in a 0.5MB workbook (including array formulas and cell formatting like font, borders, colors, etc.), minus the charts, buttons, and macros. Apparent success.

Before I attempt the task of re-creating the charts, I thought I'd ask if anyone has already done this. Surely I'm re-inventing the wheel!

Also, is this entire idea a really stupid thing to do?

[anhn]

You may tried this to see if any help instead of doing a painful job:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

[Siddharth Rout]

Also there is one more way to achieve it or at least finding where excatly is the problem...

If your workbook has 10 worksheets then make 10 copies of the workbook. From each workbook delete a sheet and save it. for example from copy 1 delete sheet1, copy 2 delete sheet 2 and so on...

Check after saving, which copy has the least size... that is the clue. Maybe that particular sheet has

1) unwanted data validation
2) shapes
3) colored cells
4) uwanted formulas etc...

Let's say it is sheet3 and in that sheet, your data range is A1:M1500

Do this

Highlight Column O. Then Press Shift + End and then click on right arrow key. Entire column from O to IV will be selected. Delete them.

Repeat the same excercise with Row 1501 till End

Now save the workbook.

Check the size. Has it reduced?

[VBAhack]

Guys,

Thanks for the suggestions. I'd already tried the 1st one before with no affect. But, this time I crawled through each sheet and removed all unwanted charts and graphics. The major culprit turned out to be a couple of graphics I'd pasted on one of the sheets. The odd thing is that when I deleted the sheet with the offending graphics, the size didn't change much. It must be that a couple of the charts I also deleted were somehow adding a lot to the size as well. Anyway, I was able to reduce it back to a respectable 1.8MB (from 9.4 MB), though it still seems like it should be smaller. Still, major success, thanks.

I do wonder, though, whether repeated saves on the same spreadsheet will continue its growth.......

[Siddharth Rout]

Guys,

Thanks for the suggestions. I'd already tried the 1st one before with no affect. But, this time I crawled through each sheet and removed all unwanted charts and graphics. The major culprit turned out to be a couple of graphics I'd pasted on one of the sheets. The odd thing is that when I deleted the sheet with the offending graphics, the size didn't change much. It must be that a couple of the charts I also deleted were somehow adding a lot to the size as well. Anyway, I was able to reduce it back to a respectable 1.8MB (from 9.4 MB), though it still seems like it should be smaller. Still, major success, thanks.

I do wonder, though, whether repeated saves on the same spreadsheet will continue its growth.......

If you would have deleted the sheet which had all the annoying graphics, you would have noticed the change.