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

[strat919]

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

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]

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]

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]

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.

 

[lrobbo314]

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.

 

[strat919]

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

 

[lrobbo314]

I just mean write it like this.

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]

Same result but a little cleaner

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]

in addition to the other replies this may help as well

ws.usedrange.Columns("A:E")

instead of

ws.Columns("A:E")

 

[strat919]

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.

Sub ExecuteAll()
Call MergeColumnsAddComma
Call GetUniquePairs
Call Text2Columns
Call deleteSheetByName
Call FindDistance
Call SortAllSheets
Call CopyXRows2Master
Call SortMasterSheetE
End Sub

[/code]