+ Reply to Thread
Results 1 to 29 of 29

speeding up code that is super slow

  1. #1
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    speeding up code that is super slow

    The code below is part of a much larger spreadsheet that I wrote using help from this site, since I have 0 prior experience. I know it is full of useless, slow, code, but until I can get someone to rewrite it completely, it's all I have to work with.

    My problem is that the code below is extremely slow to process. Basically, it grabs info from another sheet that has a possibility of having 500 lines of part numbers. There are never that many on one order, more like 40-50, but I don't know how to copy just the used cells, so I copy all visible cells in the 500 line range, after it has been autofiltered. I then paste that entire range. There are a lot of merged cells that contain data on the original sheet, but are not used on this one, so I do a lot of unmerging and merging (which I think is what's taking up all the time)

    If someone can look at this and give me some pointers as to how I can speed up this process, I would appreciate it greatly. Right now, it takes about 30 seconds or so to run.

    Thanks in advance for ALL of the help thus far and in the future, I'm sure.

    Please Login or Register  to view this content.
    Last edited by cabinetguy; 05-19-2011 at 12:23 PM.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: speeding up code that is super slow

    Basically, you want to elminate all the copy and pastes and merging of cells. To get rid of a copy and paste, just set a cell equal to where you were going to paste from. Getting rid of the merged cells is going to be up to you. Usually you can change the row height to fit extra text in the height, and for extra width you can format as Center Across Selection. Try this, it won't run a ton faster until you get rid of all that cell merging, but it may help a little.:

    Please Login or Register  to view this content.
    If you really want to improve this, move it to Access.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    this copy technique might be a little faster:
    Please Login or Register  to view this content.


    All the merge and unmerge stuff later is going to be slow.

    You could move this to the top of your macro, before all the copying starts:
    Please Login or Register  to view this content.

    Then turn it back on again as the last command before you end the macro:
    Please Login or Register  to view this content.


    I think is all you need for a grid pattern:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 05-17-2011 at 04:51 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    I'll give it a try as soon as my machine is free again. I have the "traffic" workbook open all the time so we can all see the status of new orders, which I am starting to suspect...

    The process has slowed to such a crawl that it takes about 5 minutes to run now, even after shutting down the spreadsheet and reopening.

    It's almost like a cache is filling up, since it seems to take longer each time I run it.

    Thanks for the help and like said, I'll give those changes a try when Excel wakes up

  5. #5
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    It's the traffic workbook definitely. The workbook is shared so the traffic dept. and I can see updates at all times. The original spreadsheet writes all data that is collected and then placed into row 3, onto the "traffic" workbook.

    Each time that it writes, the next time that the code is executed, it takes twice as long, until there are about 10 rows or so, at which point is is taking about 5 minutes to complete.

    The imported row has a few drop downs that it grabs from the original sheet. I'm thinking that this may be where the problem is, also I have conditional formatting for certain cells, which also seem to multiply every time a line is written...

    Yes, I am still a newbie, but I think I've figured the causes, now to find the solutions before I drain too much blood from my alcohol stream

  6. #6
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Ok, got it sped up. I removed all formatting and drop downs as well as implemented your guys' suggestions.

    I have one last question then I'll be done... for now.

    I am wanting to have a macro button in the "traffic" workbook, "traffic" sheet, that checks column W to see if the word "yes" has been inputed on any row. If so, it copies the entire row that correspond to the row that has "yes" in column "W", to the first blank row on a new sheet called "Completed"

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    Copies it or moves it?

  8. #8
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Moves it completely. Realistically, it only needs to check the first 50 rows or so, instead of the entire sheets rows

  9. #9
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Actually, JBeaucaire, I found your code on a different site that I adapted for my use. Works great. Thanks again for all your help.

    One last request for this though... Can you tell me how to add the date to the appropriate row in column X on the "Completed" Sheet so when the script is run, it puts todays date at the end of the rows that were moved over on the "completed" sheet?



    Please Login or Register  to view this content.
    Last edited by cabinetguy; 05-18-2011 at 12:37 PM.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: speeding up code that is super slow

    Did you know ?

    Please Login or Register  to view this content.
    But maybe its better to design a formatted master sheet that can be copied every time you need a new sheet based on this format. You better not use VBA for designing/formatting.
    Last edited by snb; 05-18-2011 at 01:20 PM.



  11. #11
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Ok, I just made the spreadsheets live and have run into a snag.

    The Traffic Spreadsheet is shared so myself and the traffic department can both edit it. The problem is that when I click the button to run the above script, I get an error saying that the function can not be performed because the document is shared.

    Suggestions?

  12. #12
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: speeding up code that is super slow

    Can you switch to Access? You are trying to use Excel like a database, which it was not designed for. You may find technical ways of getting around it, but it would be much easier, faster, and safer to use an application designed for multiple users to work with the same information at the same time.

  13. #13
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    I've never even opened Access, so to migrate to it would probably be quite a task. I'm speaking with no experience on the subject, so please forgive me if I sound ignorant about it; because I am =/

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: speeding up code that is super slow

    Do not despair prematurely:
    Let's start without removing anything.

    Please Login or Register  to view this content.
    Last edited by snb; 05-18-2011 at 04:16 PM.

  15. #15
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Variable not defined error on "y = Sheet.........."




    Quote Originally Posted by snb View Post
    Do not despair prematurely:
    Let's start without removing anything.

    Please Login or Register  to view this content.

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    Dim your variable at the top of the macro as the correct variable type, proper basic programming:

    Please Login or Register  to view this content.

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: speeding up code that is super slow

    Or simply remove 'option Explicit' from the module.

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    Never remove the Option Explicit from a module until you consider yourself an experienced programmer and can read VBA code as easily as you read this forum.

    Yes, removing it solves the current error message problem, and does so by turning OFF your code-checker built into Excel. When you make other simple coding mistakes in the future, which you will do, you will spend a LOT more time trying to find them without Option Explicit. With Option Explicit on, Excel will point out a majority of your syntax errors.

    The problem you have is you (?) forgot to declare your variables as the correct variable types. Declaring them is the correct solution.

  19. #19
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Ok, I can't get that code to work for whatever reason. Below is what I currently have and it works pretty well, with the exception that it doesn't put the date at the end of the moved rows on "completed" sheet.

    If you can also look at this and tell me if there's a way to prevent the initial popup that warns about the workbook not being shared during this process.

    Please Login or Register  to view this content.

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    This line of code is looking at column A on the "completed" sheet to spot the "last row" of current data and note the "next row" for the target later:
    Please Login or Register  to view this content.
    I added another sheet reference into that, underlined above, try that first.

    If column A is a bad column to use for that test, then change that to the correct column to check, column B or column W, whatever, then try again.

    I have no comment on your shared workbook issue, overcoming network security is often no small feat. Perhaps a new thread to specifically address that question without the need for the full macro as it's not really related to that.

  21. #21
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Ok, I'll try that. Do you have a suggestion for the date stamp at the end of the newly copied lines on the completed sheet?

    As far as the network security goes, it simply tells me that I am removing the workbook from being shared and asks if I am sure or not. It's a pop up that has a "yes" or "no". Is there a way to force the macro to "click" yes every time, so I don't even see the popup?

    The reasoning is that I submit an order and it populates this sheet for our shipping and receiving guy or "traffic" dept. The spreadsheet runs open on his puter all day long and when a shipment comes in, he can fill in the appropriate cells. When the job is completed, he types in "yes". It updates every 5 minutes or when saved, so because I keep the same spreadsheet open so I can schedule installations or provide customers with job status if they call, I am the one who clicks the "completed" button when I see that a job or group of jobs has been done. So essentially, I am the only one that sees that popup and since I always click "yes" to acknowledge that the spreadsheet will not be shared any more, it would be nice to not even see it.


    Quote Originally Posted by JBeaucaire View Post
    This line of code is looking at column A on the "completed" sheet to spot the "last row" of current data and note the "next row" for the target later:
    Please Login or Register  to view this content.
    I added another sheet reference into that, underlined above, try that first.

    If column A is a bad column to use for that test, then change that to the correct column to check, column B or column W, whatever, then try again.

    I have no comment on your shared workbook issue, overcoming network security is often no small feat. Perhaps a new thread to specifically address that question without the need for the full macro as it's not really related to that.

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    You might add this near the top of your macro, it is designed to suppress most alert messages and select the default answer, I can't test if this will work to suppress this particular message:

    Please Login or Register  to view this content.



    BTW, why did you quote my message into your post above? Did I miss something?

  23. #23
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    sorry, newbie error

    That line did the trick. No more pop up warnings about un-sharing. Thank you very much for your help.

    The last thing is that date stamp and I will forever leave you alone... until next time

  24. #24
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    What date stamp?

  25. #25
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    after the code copies the row and then pastes it to the "Completed" sheet, I wanted a date stamp in Column X on all the rows that were copied over.

    So basically it will show when the job was completed and then moved over to the new sheet.

  26. #26
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: speeding up code that is super slow

    Try this:
    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    PERFECT! Thank you very much for all of your help guys. Awesome experience and will definitely help us tremendously.

    Great Job!

    Now, if I could only figure out how to mark this threat SOLVED, I would be golden, but the forum rules don't seem to give me the options stated.
    Last edited by cabinetguy; 05-19-2011 at 12:07 PM.

  28. #28
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: speeding up code that is super slow

    Have a look at this 'manual'

  29. #29
    Registered User
    Join Date
    10-31-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: speeding up code that is super slow

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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