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

strat919

Board Regular
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
 

lrobbo314

Well-known Member
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
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
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
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
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
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
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
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:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top