Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    My Spreadsheet is HUGE!!! HELP (97)

    Hi all,

    I ran some great code to perform an autofilter for a really big spreadsheet (see this post: Code/Macro to make new worksheet (97))
    Post And saved parts of the results in seperate workbooks, now what has happened is the my spreadsheets are HUGE.
    Example I have one that has about 350 rows, and it is 2559kb. These workbooks are slowing down my system, and can't send more than two of them to someone via e-mail.

    I can't understand why this spreadsheet is as big as it is, does it have something to do w/ the code (I'm stumped).

    If it is the code, can I compress it somehow.

    This is a true bummber because people are waiting for this info, and need to get this resolved as soon as possible.

    As always, and suggestions, advise, expertise would be greatly appreciated.

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My Spreadsheet is HUGE!!! HELP (97)

    I'm not an Excel expert but have a look at this thread
    Francois

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: My Spreadsheet is HUGE!!! HELP (97)

    I'll just repeat some of the advice that's been given me. Maybe something here will help you a bit....

    1 - Refrain from applying formatting to entire rows or columns. For example, why would you apply the bold format to column A if you were only going to use the first 100 cells?

    2 - When you delete or clear unused columns and rows that extend beyond the boundaries of your work, <font color=blue> Save</font color=blue> the change immediately to reduce the size of the workbook.

    3 - Most everyone who writes code will use blank lines, comments and line indents. All of this is useful and customary as it makes it simpler to edit, read and understand. There is a little <font color=blue>code cleaner</font color=blue> program on one of the excel sites that will eliminate everything from the code that isn't necessary. Obviously this will trim the size of the file but I found it to be the least effective of the three noted here.

    Also, you mentioned that the file was slowing your computer. A larger file will be slower to open and close but should not be a big issue while it's running. If it's the code that's slowing things down, then I can tell you that effectively using ScreenUpdating will move things along very well. I'm still learning about this myself but I can tell you it is worth pursuing as it makes a big difference.

    Lastly, Winzip and ZipMagic, either one will compress an XLS file to a fraction of it's original size. Good for floppies and/or emailing.

    HTH
    - Ricky

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My Spreadsheet is HUGE!!! HELP (97)

    You might try copying the data from your worksheet into a new worksheet. That can help get rid of bloat.

    If you have only data, you may try Edit, Paste Special, Values into the new worksheet.

    If you want VBA code to stay with the worksheet you're sending out, copy that separately.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My Spreadsheet is HUGE!!! HELP (97)

    Here's two other ideas that have worked for me:
    1) Highlight the cells that you feel are part of the spreadsheet and click File, Print Area, Set Print Area. I have also gone so far as to delete all the rows below and columns to the right of the ones I'm using.
    2) If you are tracking changes the file will grow like crazy! (Tools, Track Changes) This can either be on by itself or can be enabled as part of sharing in the newer versions of Excel. Accept all changes and turn off tracking.
    Both of these may help.

Posting Permissions

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