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
1	42.4822	20.7458
2	42.2139	20.7397
3	42.9144	20.6897
4	42.5781	21.5803
5	42.3214	21.3583
6	42.9144	20.6897

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

	A	B	C	D	E
1	42.9144	20.6897	42.9144	20.6897	0
2	42.2139	20.7397	42.4822	20.7458	18.54432
3	42.3214	21.3583	42.5781	21.5803	21.04423
4	42.9144	20.6897	42.4822	20.7458	30.00411
5	42.3214	21.3583	42.2139	20.7397	32.49618
6	42.3214	21.3583	42.4822	20.7458	33.1735
7	42.5781	21.5803	42.4822	20.7458	43.01226
8	42.9144	20.6897	42.2139	20.7397	48.47707
9	42.5781	21.5803	42.2139	20.7397	49.73902
10	42.9144	20.6897	42.5781	21.5803	50.8234
11	42.9144	20.6897	42.3214	21.3583	53.24988

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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!