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

 

[strat919]

Sorry....maybe I was misunderstood.

only a zero distance would show up in the results if there were any duplicates in the AB column

 

[strat919]

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]42.4822[/TD]
[TD="align: right"]20.7458[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]42.2139[/TD]
[TD="align: right"]20.7397[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]42.5781[/TD]
[TD="align: right"]21.5803[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]42.3214[/TD]
[TD="align: right"]21.3583[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]

</tbody>

Sheet1​

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]42.2139[/TD]
[TD="align: right"]20.7397[/TD]
[TD="align: right"]42.4822[/TD]
[TD="align: right"]20.7458[/TD]
[TD="align: right"]18.54432[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]42.3214[/TD]
[TD="align: right"]21.3583[/TD]
[TD="align: right"]42.5781[/TD]
[TD="align: right"]21.5803[/TD]
[TD="align: right"]21.04423[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]42.4822[/TD]
[TD="align: right"]20.7458[/TD]
[TD="align: right"]30.00411[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]42.3214[/TD]
[TD="align: right"]21.3583[/TD]
[TD="align: right"]42.2139[/TD]
[TD="align: right"]20.7397[/TD]
[TD="align: right"]32.49618[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]42.3214[/TD]
[TD="align: right"]21.3583[/TD]
[TD="align: right"]42.4822[/TD]
[TD="align: right"]20.7458[/TD]
[TD="align: right"]33.1735[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]42.5781[/TD]
[TD="align: right"]21.5803[/TD]
[TD="align: right"]42.4822[/TD]
[TD="align: right"]20.7458[/TD]
[TD="align: right"]43.01226[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]42.2139[/TD]
[TD="align: right"]20.7397[/TD]
[TD="align: right"]48.47707[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]42.5781[/TD]
[TD="align: right"]21.5803[/TD]
[TD="align: right"]42.2139[/TD]
[TD="align: right"]20.7397[/TD]
[TD="align: right"]49.73902[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]42.5781[/TD]
[TD="align: right"]21.5803[/TD]
[TD="align: right"]50.8234[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]42.9144[/TD]
[TD="align: right"]20.6897[/TD]
[TD="align: right"]42.3214[/TD]
[TD="align: right"]21.3583[/TD]
[TD="align: right"]53.24988[/TD]

</tbody>

Results 190727 7.38 am
​

Row 3 and 6 are duplicates so it showed up one time as 0 distance in results

 

[Yongle]

How can there be duplicates in AB ?
- the code should remove duplicates in this line

[I][COLOR=#006400]'[/COLOR][COLOR=#ff0000]remove duplicate co-ordinates [/COLOR][COLOR=#006400]and place remaining values in array[/COLOR][/I]
    Data.Range("A:B").RemoveDuplicates Columns:=Array(1, 2)

 

[strat919]

The duplicates should not be "removed" in the code. There will be very few cases where there will exact duplicates...... but I would need to see if there were any..... and they would appear at the top of the results as the the shortest distance being zero distance.

I guess everything would work perfectly if the code simply didn't remove any exact duplicates.

I hope this makes sense to you

 

[Yongle]

Why not simply delete the line that removes duplicates ?

 

[strat919]

I tried that, but got an error.... I will try it different ways

 

[strat919]

I didn't delete the entire line.

It works perfectly now Thank you so much for your help Mr Yongle!