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
 
Sorry....maybe I was misunderstood.

only a zero distance would show up in the results if there were any duplicates in the AB column
 
Last edited:
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
AB

<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



ABCDE

<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
 
Last edited:
Upvote 0
:confused:
How can there be duplicates in AB ?
- the code should remove duplicates in this line
Code:
[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)
 
Last edited:
Upvote 0
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:)
 
Last edited:
Upvote 0
Why not simply delete the line that removes duplicates ?
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top