Code used to work, now takes forever and crashes?

  • Hi All


    I have a simple line of code:


    Code
    ThisWorkbook.Sheets("Sheet1").UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")


    Pretty straight up, takes the data in Sheet 1 and puts it in Sheet 2.


    This has worked for ages and ages. However just today Excel when processing will sit on this line for ages, the window will go "no responding" etc and just sit there.


    Users usually close Excel at this point.


    I have tried all the variations of the above that I can think of. But all act the same.


    I did leave it unresponding once, and after about 30 minutes it completed the action.



    The data set is usually A to L and never more than 40 rows or so.



    Any ideas on whats going on?

  • Re: Code used to work, now takes forever and crashes?


    See if this helps


    Apart from obviously restarting your PC and checking that there aren't any memory hogging softwares running in the background you could clear the Excel cache folder.


    Using Windows 7 64 Bit and Excel 2010, mine is located in the folder C:\Users\Current User\AppData\Roaming\Microsoft\Excel (replace Current User with the actual logged in username). AppData folder is actually hidden by windows by default so you will have manually type that in the address bar.


    Using windows command prompt change attribute of all hidden files to unhidden inside C:\Users\Current User\AppData\Roaming\Microsoft\Excel and then delete everything inside the Excel folder (making sure Excel is not running at the time).


    Come one folder higher and check that the Folder size of Excel folder is zero bytes.

  • Re: Code used to work, now takes forever and crashes?


    Try using CurrentRegion instead of UsedRange. The last cell which determines the UsedRange may become "distorted" making the file size larger than expected.

  • Re: Code used to work, now takes forever and crashes?



    Ah ok I was wondering something like this? Would this be happening of lots of different peoples computers though? There are a few other simple lines of code that have also started giving me grief, usually around copying also, those I have fixed by just writing more efficient code. But if this temp folder is the cause then that would explain them all?


    Dont know how I am going to do this on everyones computers though haha.



    Quote from royUK;703089

    Try using CurrentRegion instead of UsedRange, the UsedRange. The last cell which determines the UsedRange may become "distorted" making the file size larger than expected.


    Whats the difference between CurrentRegion and UsedRange? I'll give it a go but I'm not hopeful since I have tried other variations other than UsedRange, including using Range("A1:L40") etc with the same results.

  • Re: Code used to work, now takes forever and crashes?


    Quote from stildawn;703186

    Ah ok I was wondering something like this? Would this be happening of lots of different peoples computers though? There are a few other simple lines of code that have also started giving me grief, usually around copying also, those I have fixed by just writing more efficient code. But if this temp folder is the cause then that would explain them all?


    Dont know how I am going to do this on everyones computers though haha.


    Well if the solution I have given helps, you could create a simple .bat file to clear the temp folder and send it out to everyone. Of course, the .bat file will have to be customized at least once for each PC Login ID, but end of the day, each user need not go the folder everyday to clear the temp folder.


    Do let us know what worked?

  • Re: Code used to work, now takes forever and crashes?


    I just tried clearing the folder. There wasnt much in there, and it didnt help.


    I'm wondering if there is something wrong with the file itself? It takes longer than most to open. Its just blank though except for lots of modules and coding etc. Its only 568kbs in size.

  • Re: Code used to work, now takes forever and crashes?


    Yeah I have tried that. Same thing.




    I really doubt its the individual codes fault, its happen on lots of the runs now. Basically the file is a organiser of a bunch of different formatting code that turns invoices we recieve in excel(also CSV, TXT etc) format into a special template which we can push into our system.


    So the file basically loads up, asks for the original raw data file, and the the user selects (all via a userform) which client its for and then it runs through the specific code.


    Pretty much all of the different codes are slowing down/crashing out now?


    Is there a way to export the code structure into a new file or something? I dont really want to have to rewrite the whole thing.

  • Re: Code used to work, now takes forever and crashes?


    run:


    Code
    sub M_snb()
       msgbox  ThisWorkbook.Sheets("Sheet1").UsedRange.address
    
    
       msgbox ThisWorkbook.Sheets("Sheet1").cells(1).currentregion.address
    end sub
  • Re: Code used to work, now takes forever and crashes?


    Well running it from workbook opening it gets this result:


    $A$1


    $A$1




    I also ran the code up to the line in the OP (where it crashes on this specific code) and the result was:


    $A$1:$L$9


    $A$1:$A$8




    This was run on this data:


    [Blocked Image: http://i.imgur.com/0yXKFWU.jpg]


    So you can see, the Usedrange works but it has one dirty row, row 9 which is empty but is picked up by UsedRange.


    While CurrentRegion gets the correct number of rows but not columns.




    Its not just this now though, the file seems to hang and generally run like crap on most of the code now.



    Hope this helps, really appreciate you guys looking at this for me, its got me puzzled.

  • Re: Code used to work, now takes forever and crashes?


    What is in that range? Are there text boxes or objects that have 0 height?



    I've seen workbooks that people use copy / paste on not knowing that the area selected contains objects that they can't see. I once had to clean a workbook that had in excess of 10000 objects all with 0 height.

  • Re: Code used to work, now takes forever and crashes?


    Well the data in question is the screen shot above.


    In regards to objects though, do they survive on whole sheet deletes? like "cells.Delete"?


    Cause if so that could be the cause? Is there away to "clean" out all objects? There certainly is no need for any to be on the sheets.

  • Re: Code used to work, now takes forever and crashes?


    Well it's just one of a number of problems.


    From the immediate window type something like ?worksheets("sheet1").shapes.count




    ... What is the file size?

  • Re: Code used to work, now takes forever and crashes?


    [INDENT]Did you remove all conditional formatting ? Yes well there is none
    Did you remove all named ranges ? None of these either
    Did you remove all VBA code ? No I didnt, as the code is the whole point of the file, its a holder of lots of different coding.
    Did you remove all external links ? None of these either[/INDENT]

  • Re: Code used to work, now takes forever and crashes?


    Quote from snb;703581


    Did you remove all VBA code ?




    Hmmmm..... I've had something like this i think.


    1. In your workbook. EXPORT the macro pages to file then "REMOVE" the macro pages.
    2. Save the workbook .... do a saveas to a new name....
    ....Just an aside, my project workbooks always had 2 names first was "thisisaproject.xls" was used by others and "thisisaproject_UnderConst.xls was the workbook that I used to make improvements before rolling out to users.


    3. With the code removed from the workbook.... close it and reopen and then reimport the vba modules.




    See if that helps.

  • Re: Code used to work, now takes forever and crashes?


    have you any hidden sheets ?
    have you any pivottable ?
    have you any charts ?
    have you any eventcode without application.enableevents =false ?
    have you a lot of array formulae ?
    have you references in formulae to whole rows/columns ?

Participate now!

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