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

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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.
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
That did the trick:) I didn't change the screenupdating, but will. Why does that make such a difference......just curious.

Thanks a bunch!
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,942
Office Version
2013
Platform
Windows
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
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
350
Office Version
2016
Platform
Windows
in addition to the other replies this may help as well
Code:
ws.usedrange.Columns("A:E")
instead of
Code:
ws.Columns("A:E")
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top