File Size Bloat

    I have a VBA Excel system that is 55MB in size. There are some worksheets with a lot of data which make up most of the size.


    i wrote a utility to delete each worksheet in turn, save the file then record the new file size after each worksheet delete. It runs all the way down to just a "dummy" blank worksheet.


    I created a copy of my system then ran on my utility on this copy. At then end I have a file with just the dummy worksheet and all the VBA code.


    The copy after all worksheet deletion is 7MB in size. Is this size therefore all attributable to the VBA code?


    Thanks in advance.

    Sometimes we unknowingly create blank unused rows and columns which Excel recognizes as not being blank. To check if that is what happened in your case, select cell A1. Then hold down the CTRL key and press the END key. This will take you to the last recognized cell in the sheet. If it takes you well below and well to the right of any actual data, delete the extra rows and columns and immediately save the file. Close it and re-open it and repeat the steps to see where they take you. Also check the file size again. Another possibility is if you have formulas in a very large number of rows and/or columns. Sometimes they return a blank cell but Excel recognizes the cells as being used. You could attach a copy of your file so we can check it out.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    You can try running this code


    Thanks to you both for your posts. However as described I now have a 7MB workbook with nothing but VBA code and a blank, brand new worksheet. To be clear - this worksheet is just added and therefore does not suffer from the unused/blank cells problem.


    I guess my question is this: does 7MB sound right for just the VBA code?


    I have many classes and modules so I expect it to have some size.


    Thanks

    You could always export the code modules and see how large the exported files are.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login