Custom sort not working correctly on larger file

[Bassie]

Hi,

Is there a way to make custom sort work correctly on larger files? I created a custom list but both in VBA and in regular excel it does not work correctly on larger files, while it does work perfectly fine on smaller files.
Is this a known issue and is there a way to resolve this?

Current VBA code:

MPListGL = "IT, DE, NL, UK, SE"
vSortlistGL = Array(MPListGL)
Application.AddCustomList ListArray:=vSortlistGL
ActiveSheet.UsedRange.sort key1:=GL, Header:=xlYes, ordercustom:=Application.CustomListCount + 1

Kind regards,
Bassie

 

[Bassie]

Try out the following on a copy of your file. Assumes column A holds the values to sort on. It inserts a column in B:B, evaluates the first 2 characters from column A, sorts on column B, then deletes column B. I tested it on data with 1M rows & 90 columns. It took a while (30 seconds+) but it did seem to work.

EDITED

Option Explicit
Sub Bassie()
    Application.ScreenUpdating = False
    Dim t As Double: t = Timer
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("Sheet1")   '<~~ change to actual sheet name
   
    Dim LRow As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
   
    With ws.Range("B1")
        .EntireColumn.Insert
        .Value = "temp"
    End With
   
    With Range("B2:B" & LRow)
        .Value = Evaluate("Left(" & .Offset(, -1).Address(, , , 1) & ",2)")
    End With
   
        Set rng = ws.Range("A1").CurrentRegion
        Application.AddCustomList ListArray:=Array("IT", "DE", "NL", "UK", "SE")
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=ws.Range("B1"), _
            CustomOrder:=Application.CustomListCount
            .SetRange rng
            .Orientation = xlTopToBottom
            .Apply
        End With
    ws.Range("B1").EntireColumn.Delete
   
    MsgBox Timer - t
    Application.ScreenUpdating = True
End Sub

That works wonders, thank you so much!! I dont mind that it takes 30 seconds as this macro runs ones a week for a total of like 4 mins and I honestly think you cant make it fast than this on files that large. Again thank you :D

Regards
Bassie

 

[kevin9999]

Glad it worked out for you, and thanks for the feedback