+ Reply to Thread
Results 1 to 5 of 5

Excel VBA File to Big

  1. #1
    Registered User
    Join Date
    04-17-2011
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    27

    Exclamation Excel VBA File to Big

    Problem

    I recently Made an adjustment to my file. I pretty much doubled the file size. I then Deleted the adjustment because my file was running very slow. However even after deleting the adjustment it is still running slow.... Any Ideas??

    I dont know what to do??



    -Crebsington

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Excel VBA File to Big

    Hi

    Did you clear out the additional data, or really delete the rows / columns. If you have just cleared out the data, and are using something like usedrange to determine the area to action, it may still be working on the full size.

    Bit hard to suggest much when we don't have any idea of what your code is doing, or how it was written....

    rylo

  3. #3
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Excel VBA File to Big

    I agree with rylo that it would be a lot easier with a sample.

    However, things to look at:-
    Avoid VLOOKUPs. Use INDEX, where possible.
    Check the used range by going to the end (CTRL-End). Delete empty rows/columns.
    If you have lots of files/programs open it can slow things down.
    Avoid VOLATILE functions. These are functions that are calculated during every recalculation. Most are only recalculated when you change their input. (Google for a list of Excel's VOLATILE functions - it varies by version)
    Conditional formatting is volatile. Don't use too much in big files!
    If you have referenced who columns (e.g. A:A, A:C, or A1:A65000) consider revising to use just the required rows (i.e. A1:A300). Dynamic Named Ranges can help with this.

    If it's got macros, turn off screen updating during the macro execution. Consider turning off Calculation during macro execution (may need to turn them on again at some point if you rely on cells being calculated to determine the macro's actions).

    There is lots of advice on the web re spreadsheet design, volatile vs non-volatile, etc.

    Cheers, Rob.

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Excel VBA File to Big

    Also, rework your code to remove all .Select and .Activate statements as they make the code run a lot slower than declaring Range and Sheet variables.
    As previously noted, it would be strongly advised that you provide the forum with the code so that it can be looked at by the community to best find a solution for you.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  5. #5
    Registered User
    Join Date
    04-17-2011
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    27

    [SOLVED] Re: Excel VBA File to Big

    Deleted the sheet and all the functions and it brought my file size way back down!

    Thanks guys sooo much for your time and efforts!


    -Crebsington

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1