File size problem: standard remedies not working.

H

hojikuru

I have a file I've shared/unshared multiple times. I have conditional
formatting, about 20 custom views, 30 simple VBA scripts, and a whole
host of error-checking formulas like
=IF(Z13=0,"",IF(Z24=0,"",IF(Z35=0,"",IF(Z57=0,"",IF(Z63=0,"",IF(Z46=0,"",IF(Z69="","",Z13+Z24+Z35+Z57+Z63+Z46+Z69)))))))

My filesize keeps growing.

I've tried the following:
1. resharing, clearing the "keep history" checkbox, and unsharing
again
2. running Rob Bovey's codecleaner http://www.appspro.com/
3. resetting the lastcell reference in my workbook open module
Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

None of the above changed the file size at all (3,147 KB before, 3,147
KB after).

Any other suggestions?

Thanks,
Michael Lambert.
(e-mail address removed)
 
T

Tom Ogilvy

Doing step 3 does nothing unless you delete entirerows and entirecolumns of
what you consider to be unused space. If you think you worksheet ends at
row 200 for example, but when you are at row 200, the scrollbar on the right
is not at the bottom of the "tray", then Excel does not see row 200 as the
bottom of your worksheet. Go to the name box and type

201:65536 <cr>

then do Edit=>Delete

for columns

AA:IV <cr>
Then do Edit =>Delete

then save the file

Repeat on each sheet.
 
H

hojikuru

Thanks, Tom.

I checked all of my sheets, and none of them have this problem.

Are there any other things I could try to reduce the filesize? Some
common problems you see?

R/
ML
 
S

Steve Hieb

Are there any other things I could try to reduce the filesize? Some
common problems you see?
Click to expand...

This is usually simple and has worked for me several times. Select
all your worksheets, right-click on sheet tab, select Copy to a New
workbook. You'll also need to import any modules, forms, etc. You
should notice a modest size reduction. You might need to resolve any
formula links and watch for issues w/ any ActiveX objects used on the
sheets.

A more painful, but usually much more effective approach is similar.
Copy all the cells on each sheet to new/empty sheets and paste only
the formulas. Then carefully reformat everything and recreate any
ActiveX or form buttons, etc.

Here's why. You can test this for yourself. Usually many formats are
tried and later abandoned during a project's development. Each time a
new format is used it usually increases the file size ... over time
this can be excessive. The real mystery why the file remains bloated
even if the format is later not used. Example: if you format a cell
w/ red text then decide to change it back to Automatic you've just
increased the file size - don't ask me. Obviously, format changes
often happen over and over during the creation process causing
bloating. FYI, it seems to happen w/ most formatting, not just
colors. Although sometimes painful, I've seen substantial reductions
using this technique. Also have learned to be very selective w/
formating from the beginning (i.e. do it last).

Other things to consider. I'd make sure you've deleted any unused
named ranges and objects (e.g. charts, text boxes, images, etc.).
Consider writing code to list what's on each sheet ... maybe some
things are there, but not visible to the naked eye. You could use
code to quickly delete them all if you know there shouldn't be any.

HTH,
Steve Hieb
 
Top