Page 1 of 5 123 ... LastLast
Results 1 to 10 of 47

Thread: Should this code be written differently? Takes way longer than sorting each sheet manually

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Should this code be written differently? Takes way longer than sorting each sheet manually

    I have some code to sort all sheets column E low to high. I have 18 sheets each with about a million rows. I can sort one sheet manually and it takes maybe 5 seconds. When I use this code, the 18 sheets take almost 10 min.

    I will have any number sheets, depending on previous calculations. In this instance, it is 18.

    Also, it takes way more memory than I would expect.

    Thanks for any help

    Code:
    Sub SortAllSheets()
       'Descending sort on A:E using column E, all sheets in workbook
       Dim ws      As Worksheet
       For Each ws In Worksheets
          ws.Columns("A:E").Sort Key1:=ws.Columns("E"), Order1:=xlAscending
       Next ws
    End Sub

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Does changing 'Key1' to ws.Range("E1") help at all?

    Also, put Application.ScreenUpdating = False at the beginning of the code, and Application.ScreenUpdating = True at the bottom.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    That did the trick I didn't change the screenupdating, but will. Why does that make such a difference......just curious.

    Thanks a bunch!

  4. #4
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    I actually jumped the gun. When I ran your code changes, I sorted the 13 sheets when they were already sorted....I ran all the macro sequences again and it was still much slower than sorting each column manually.
    Last edited by strat919; Jul 20th, 2019 at 11:41 PM.

  5. #5
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Don't know about the sorting. You might want to try turning Application.Calculation = xlCalculationManual at the beginning and Application.Calculation = xlCalculationAutomatic at the end, along with the ScreenUpdating.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  6. #6
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Do you mean turning......adding?

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    I just mean write it like this.

    Code:
    Sub SortAllSheets()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    'Descending sort on A:E using column E, all sheets in workbook
    
    Dim ws      As Worksheet
    
    For Each ws In Worksheets
        ws.Columns("A:E").Sort Key1:=ws.Range("E1"), Order1:=xlAscending
    Next ws
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,804
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Same result but a little cleaner
    Code:
    Sub SortAllSheets()
    with Application
       .ScreenUpdating = False
       .EnableEvents = False
       .Calculation = xlCalculationManual
    end with
    'Descending sort on A:E using column E, all sheets in workbook
    
    Dim ws      As Worksheet
    
    For Each ws In Worksheets
        ws.Columns("A:E").Sort Key1:=ws.Range("E1"), Order1:=xlAscending
    Next ws
    with Application
       .ScreenUpdating = true
       .EnableEvents = true
       .Calculation = xlCalculationautomatic
    end with
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #9
    Board Regular
    Join Date
    Jan 2018
    Posts
    228
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    in addition to the other replies this may help as well
    Code:
    ws.usedrange.Columns("A:E")
    instead of
    Code:
    ws.Columns("A:E")

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Thanks for your help everyone Unfortunately, nothing seems to make any difference. Maybe excel handles running macros concurrently differently rather than one at a time. Seems like when I run the macro I set up to execute all the macros, it's not totally finishing one macro before starting the next.

    I can run all macros with around 4500 entries, which ends up with 13 sheets of about 1 million rows per sheet after calculations. Takes 5 minutes on my machine.

    I have tried 12000 entries, which results in about 30 sheets of a million per sheet. Excel crashes every time. But..... I can run each macro manually and it works just fine. So there is a difference in the way excel handles automating multiple macros.

    Maybe there is a way to write the "Master" macro so excel must totally finish a macro before starting the next. Here's what I have for the "Master" macro.
    Code:
    Sub ExecuteAll()
    Call MergeColumnsAddComma
    Call GetUniquePairs
    Call Text2Columns
    Call deleteSheetByName
    Call FindDistance
    Call SortAllSheets
    Call CopyXRows2Master
    Call SortMasterSheetE
    End Sub
    [/code]
    Last edited by strat919; Jul 21st, 2019 at 08:53 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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