Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File size problem: standard remedies not working.
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy wrote in message om... 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,"",I F(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File size problem: standard remedies not working.
That is the most common. I think any other would pale in comparison.
-- Regards, Tom Ogilvy wrote in message om... 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy wrote in message om... 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,"",I F(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File size problem: standard remedies not working.
Are there any other things I could try to reduce the filesize? Some
common problems you see? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column pixels to the standard size? | Excel Worksheet Functions | |||
Still problem with larg file size... | Excel Discussion (Misc queries) | |||
File Size Problem | Excel Worksheet Functions | |||
File Size a real problem! | Excel Discussion (Misc queries) | |||
problem working with time data imported from text file | Excel Worksheet Functions |