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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

lrobbo314

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

ADVERTISEMENT

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,819
Office Version
365, 2019, 2016
Platform
Windows

ADVERTISEMENT

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
19,090
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
352
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,110
Messages
5,509,266
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top