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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,780
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,780
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,780
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,974
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,102,869
Messages
5,489,381
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top