How do you clean up bloated workbooks? (Excel 2000 SP1a)

Topic

New Reply

Oh great Excel Gurus Lounging around

One of my users has an Excel workbook with about 1/2 dozen sheets yet the file is bloated to 10MB. I noticed on a lot of the sheets if you hit CTRL-END, it will take you to the appx 64000th row. I assuming that the user formatted entire columns and rows when it wasn’t necessary. Is there a way to clean this up easily that will reduce the size of the workbook. After selecting the empty area, Isn’t it Edit, Clear, All? Do you need to perform Edit, Clear, Formats, then Edit Clear Contents?

Let me know,

Reply | Quote

Replies

You need to select all of the unused columns or rows and then use Edit/Delete to delete them. Then save the workbook. Also, make sure that Tools/Track Changes is not turned on.

Reply | Quote

Thanks for the quick reply. It’s now 6.2MB instead of 10MB.

Am I correct in assuming when someone formats an entire column, it bloats it by putting formatting information to the 65536th row even though there’s really nothing in the cells?

I’ve seen workbooks bloat to 20MB, 30MB. Then the user wonders why their workbook explodes, takes an eon to fire up, and is generally sluggish, never mind the 15 other apps they have going at the same time.

Reply | Quote

I’m not positive, but I think that it depends on what formatting was done. Setting the numeric format for an entire column should not bloat the workbook. However, I think that some other formats like borders might.

Reply | Quote

From what I can tell formatting an entire column the same way adds less to the size of the workbook than formatting a column partially, provided the column is formattted as single range.

Andrew C

Reply | Quote

You might also try Rob Bovey’s Code Cleaner Add-In. Might or might not help reduce the filesize, depending on the amount of code.

Edited Mar 13th 2004 to update link

Reply | Quote

Or Edit, Clear, All, or the lazy man’s way (that would be me) delete all unused rows and columns, which may be what Legare means. Another thing to note is that the mere presence of VBA modules will add considerable size, I once deleted five empty VBA modules and gained 15 MB.

Reply | Quote

That’s an interesting experience. I have a problem wherein I have several very large (14 – 20MB) Excel files that contain pivot tables (up to eight in a file, each on its own worksheet). We use a lot of code to reformat, update and refresh these tables, and each table has its own cache (we tried sharing caches, but that didn’t work for us). There are no blank modules, but there probably is some code and/or some entire macros that could be eliminated. Would you have any idea if that might affect file size as well?

Also, each file has three modules: one for code that prints the tables, one for code that updates the tables and one for code that modifies (extensively) the database. Do you think putting all the code into one module would reduce file size?

Thanks in advance.

Reply | Quote

If you test by just adding blank modules to a new WB it doesn’t show much impact, but under certain circumstances (beyond my ken) a lot of deleted code can hang onto the module, which you can see if you save the module to a *.bas file. One thing to consider on modules which have been heavily modified is to save to .bas, edit out the crud, delete the module and reimport the .bas file. And don’t blame me if you lose anything.

If the code was one time use and you are obsessive about WB size, delete the module.

Reply | Quote

Hi John,

Thanks very much for your reply. I think your suggestion about creating a .BAS file and editing in that mode is really creative and I’m gonna try it first thing next week. Out of curiosity, how did you learn about deleted code “sticking around” in a module? I would never have thought to even ask that question.

Thanks again and best regards,

Reply | Quote

AFAIK saving as BAS file is the process that actually gets rid of (hidden) edits in your code. Non need to edit the BAS file after that, just delete the module and import it again. This is what Rob Bovey’s code cleaner automates for you. Please note, that class modules behind sheets (and the Thisworkbook module) cannot be cleaned (you cannot delete them).

Reply | Quote

Have you tried
Tool-Options-Calculation
..uncheck ‘save external link values’?

zeddy

Reply | Quote

Forget my last reply!

I just remembered:
1. right-click on your existing pivot table.
2. From the menu select ‘Table Options..’
3. In the bottom panel ‘Data Options..’ just uncheck ‘Save data with table layout’

..This has dramatically reduced file sizes for me.
(I reduced a 30MB file to 300Kb )

zeddy

Reply | Quote

Forget my last reply!

I just remembered:
1. right-click on your existing pivot table.
2. From the menu select ‘Table Options..’
3. In the bottom panel ‘Data Options..’ just uncheck ‘Save data with table layout’

..This has dramatically reduced file sizes for me.
(I reduced a 30MB file to 300Kb )

zeddy

Reply | Quote