VBA custom sort

[aurelius89]

I have recorded the following macro while doing my custom sort:

Sub sortMembers()


Dim ws As Worksheet: Set ws = Worksheets("RefindData")
    
ws.sort.SortFields.Clear
    
ws.sort.SortFields.Add Key:=Range( _
        "A2:A9998"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    
    
ws.sort.SortFields.Add Key:=Range( _
        "C2:C9998"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    
    
ws.sort.SortFields.Add Key:=Range( _
        "F2:F9998"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    
    
    With ws.sort
        .SetRange Range("A1:P9998")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




End Sub

I have cleaned it up a little by removing selects and defining my worksheet as a variable. Any other way this can be made more efficient or cleaned up?

 

[JackDanIce]

One suggestion, but everyone has their own way/style/preferences:

Sub sortMembers()

    Dim LR  As Long
    Dim rng As Range
    Dim wks As Worksheet
    
    Application.ScreenUpdating = False
    
    'Set wks = Sheets("RefinedData")
    With ActiveSheet
        LR = .Cells(.Rows.Count, 1).End(xlUp).row - 1
        Set rng = .Cells(1, 1).Resize(LR + 1, 16)
        .Sort.SortFields.Clear
        .Sort.SortFields.add key:=.Cells(2, 1).Resize(LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.add key:=.Cells(2, 3).Resize(LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .Sort.SortFields.add key:=.Cells(2, 6).Resize(LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        
        With .Sort
            .SetRange rng
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    
    Application.ScreenUpdating = True
    
    Set rng = Nothing
    Set wks = Nothing
        
End Sub