+ Reply to Thread
Results 1 to 19 of 19

Solving a slow excel file which uses over 1GB of memory.

  1. #1
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Solving a slow excel file which uses over 1GB of memory.

    Hello all,

    I have a very slow excel file which I am having trouble trying to work out what the cause of this is.
    The file itself is not very big however upon opening the file the memory usage goes up to around 1.2 GB, I have checked my conditional formatting and formulas however I cannot find any obvious reason for the slow down.
    There are 5 sheets inside however and there are a fair few formulas but I cannot think that any of them would cause such a huge slow down.
    There are no VBA codes inside the sheet either.

    Could somebody help me investigate this?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Solving a slow excel file which uses over 1GB of memory.

    In each sheet, place the cursor in (e.g.) A1. Enter CTRL-SHIFT-Down arrow. What row number does it stop at?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    sheet 1 stops at row 8
    sheet 2 also at row 8
    sheet 3 stops at row 2
    sheet 4 stops at row 3
    sheet 5 stops at row 23

    Where are we going with this?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Solving a slow excel file which uses over 1GB of memory.

    I was looking for file bloat... I was expecting you to say 1048576.... can you double-check by grabbing the vertical scroll bar handle and dragging it down to the bottom. What row does it stop at?

    Can you do the same for columns : CTRL-SHIFT-Right arrow?

  5. #5
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    I understand now
    As for your questions,:
    Horizontal bar:
    Sheet 1 43
    sheet 2 64
    sheet 3 797
    sheet 4 303
    sheet 5 35

    ctrl shift right:
    sheet 1 AL
    sheet 2 AL
    sheet 3 AL
    sheet 4 I
    Sheet 5 A

    Thanks for your help so far

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.

    post your file.

  7. #7
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    I thought I did in my first post... apparently not one moment....

    EDIT:

    Should be attached now
    Attached Files Attached Files
    Last edited by bennieboi20; 10-21-2016 at 08:53 AM.

  8. #8
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.

    For better result post your file.

  9. #9
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    should be attached now

  10. #10
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.

    your if & weeknum formula slow down the file. You can re-structure calculation. why not using "Name Range".

  11. #11
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.


  12. #12
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.


  13. #13
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.


  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Solving a slow excel file which uses over 1GB of memory.

    I started by deleting rows and clearing cells. Some improvement, but still way slower than expected. At one point, I had one of the sheets all cleared and noticed a small picture, which led me to wonder if there are other pictures. I ran VBA code like this
    Please Login or Register  to view this content.
    and found that there are about 60k objects in one sheet and 120k objects in another sheet. Further inspection finds that they are almost all empty objects. I would expect that if you added something like
    Please Login or Register  to view this content.
    to the above procedure, it would delete all of those shapes and your file will work much faster. On my machine, I waited ~30s and only deleted 5k of the 180k shapes, so this procedure may take a while to run and will give Excel/VBA not responding errors until it is finished. So, it will require some patience. You might want to set up the loops to delete 50 at a time or something like that rather than try to delete all 180k shapes at once. Or, perhaps rebuild the spreadsheet from a clean file. I suspect that, once you get rid of all of those shape objects, your file will be much more responsive.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  15. #15
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    [QUOTE=MrShorty;4506571]snipQUOTE]

    You my friend are a life saver, I also noticed these very small pictures and could not work out where they had come from, I will run your vba over night and see what happens on Monday.
    I will post my results either way


    EDIT:

    I cannot seem to get the VBA running, am I supposed to combine them or run them separately, I have very little experience with VBA forgive me
    Last edited by bennieboi20; 10-21-2016 at 10:19 AM.

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Solving a slow excel file which uses over 1GB of memory.

    Yes they should combine into a single procedure:
    Please Login or Register  to view this content.
    Once you get it entered and debugged and you are convinced that it will work correctly, you can remove the Stop statements and let it run.

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Solving a slow excel file which uses over 1GB of memory.

    I think that I managed to get all the unseen objects out of your workbook. I ran the code on each worksheet that I found on http://www.mrexcel.com/forum/excel-q...following.html
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  18. #18
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solving a slow excel file which uses over 1GB of memory.

    Dear bennieboi20
    I tried to more faster. Refer attachment file modified.
    Hope your problem resolved.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    10-12-2016
    Location
    London
    MS-Off Ver
    2016
    Posts
    34

    Re: Solving a slow excel file which uses over 1GB of memory.

    Quote Originally Posted by newdoverman View Post
    snip
    Quote Originally Posted by MrShorty View Post
    snip
    Thanks a lot for your help both of you.
    The sheet is now running a million times faster and I have been able to reinsert my conditional formatting for making "0's" blank since this list is printed every day it makes it look nicer.
    I know where to come next time I have an issue and I have added rep for all of you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Thrashing VBA Memory Leaks. Memory Loop Problems. Uncleared Memory in .Match Range Object
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2017, 05:17 PM
  2. Replies: 3
    Last Post: 08-05-2014, 11:09 PM
  3. Excel Slow down and Memory hog!!
    By mongoose36 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-11-2014, 09:49 AM
  4. Replies: 3
    Last Post: 12-14-2011, 04:58 PM
  5. Replies: 1
    Last Post: 12-08-2011, 08:52 AM
  6. Excel Memory Use & File Size
    By Philb1 in forum Excel General
    Replies: 19
    Last Post: 11-02-2011, 05:20 PM
  7. Routine running slow (memory leak?)
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2006, 07:25 AM

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