Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Growing" File Size: A Solution
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. Hit alt-F11 to get to the VBE hit ctrl-g to see the immediate window Type the statement below in the 'immediate' and hit enter: ?activesheet.shapes.count 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() Dim wks As Worksheet Dim shp As Shape For Each wks In Worksheets For Each shp In wks.Shapes shp.Delete Next shp Next wks End Sub 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
how to increase size of "name box" and "contents of cell " displa. | New Users to Excel | |||
Solution "Your changes could not be saved" "The document may be read-only or encrypted" | Excel Discussion (Misc queries) | |||
any solution to the "too many cell formats error" in excel? | Excel Discussion (Misc queries) | |||
circular files; iterating to a "correct" solution | Excel Discussion (Misc queries) |