"Growing" File Size: A Solution

[John Childs]

I had a 'growing' Excel file that reached 60MB but following Dave Peterson's
expert advice, it got shrunk from 60MB to 1.58MB!

I'd like to share the solution that Dave Peterson gave that worked for me.

Dave asked if I had any "objects" and I replied, "Only 11". With Dave's
help it turned out I had 43000 of them & 42,989 were hidden. Ron de Bruim
says the following are also shapes or objects:
1. ActiveX controls (Control Toolbox) or a linked or embedded OLE objects
2. Controls from the Forms toolbar
3. Controls from the Drawing toolbar

I'm running a VBA program that was constantly erasing and then copying data
to a spread sheet. Files can get large for many reasons. I had stripped my
spreadsheet. Erased all VBA macros. Dumped all temp files. Last Cell &
UsedRange didn't appear to be issues. Not tracking changes. No pivot tables.

To see if I had hidden objects, Dave had me run the Macro below:
Open your troublesome workbook.
If this 'counting' macro yield a large number, keep going. Dave then gave
me the macro below which will select ALL macros on a sheet at one time, and
then you hit DELETE.
Sub testme()
If you want to make all the Shapes or Objects visible, then Dave gave this
macro:
Sub testme()

Dim wks As Worksheet
Dim shp As Shape

For Each wks In Worksheets
'MsgBox wks.Shapes.Count
For Each shp In wks.Shapes
shp.Visible = True
Next shp
'MsgBox wks.Shapes.Count
Next wks

End Sub

Here's another of Dave's macros that will go through each sheet and delete
all shapes and objects:

Sub testme()

Dim wks As Worksheet
Dim shp As Shape

For Each wks In Worksheets
'MsgBox wks.Shapes.Count
For Each shp In wks.Shapes
shp.Visible = True
Next shp
'MsgBox wks.Shapes.Count
Next wks

End Sub

"Be aware that comments are shapes, cells with data validation are shapes,
autofilters are shapes. So you may want to look at Ron de Bruin's site if
any of your worksheets have comments, data|validation, or autofilter.

If you have any of that stuff on any sheet, then don't use the one above."

From Ron de Bruin's site:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

Thanks Dave. Great Job!! My Applicant Tracking System works and everyone
in the office is relieved and impressed!!!