Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    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. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

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

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

    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •