Sort on 'n' no. of Fields (VBA)

Vishesh's picture
Copy-Paste the following code in a general module and run. Alternatively, download the attached file to see how it works. You can pass field names (Column headers) or Column Index as function parameters.

Sub TestIt()
 
    Call CustomSort(Sheet1.UsedRange, True, "F1", "F4")
 
     'or

    'Call CustomSort(Sheet1.UsedRange, True, 1, 4)

End Sub
 
 
 
Sub CustomSort(rngSortWithHeader As Range, blnSortAscending As Boolean, ParamArray arrFlds() As Variant)
 
    'Parameters
    'rngSortWithHeader      - range including header
    'blnSortAscending       - sort order of all fields; its same for all fields
    'ParamArray arrFlds()   - field names inclosed in "" or column no. separated by comma
    
    Dim wks         As Worksheet
    Dim rngHeader   As Range
    Dim rngRef      As Range
    Dim rngSortFld  As Range
    Dim x           As Integer
 
    Set wks = rngSortWithHeader.Parent
    Set rngHeader = rngSortWithHeader.Rows(1)
    With rngSortWithHeader
        Set rngRef = Intersect(.Columns(1), .Columns(1).Offset(1))
    End With
    wks.Sort.SortFields.Clear
    For x = LBound(arrFlds) To UBound(arrFlds)
        On Error Resume Next
            Set rngSortFld = Nothing
            Set rngSortFld = rngRef.Offset(, Application.WorksheetFunction.Match(arrFlds(x), rngHeader, 0) - 1)
        On Error GoTo 0
        If rngSortFld Is Nothing Then
            Set rngSortFld = rngRef.Offset(, arrFlds(x) - 1)
        End If
        If blnSortAscending Then
            wks.Sort.SortFields.Add Key:=rngSortFld _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Else
            wks.Sort.SortFields.Add Key:=rngSortFld _
                , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        End If
    Next x
    With wks.Sort
        .SetRange rngSortWithHeader
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Set wks = Nothing
    Set rngHeader = Nothing
    Set rngRef = Nothing
    Set rngSortFld = Nothing
 
End Sub
AttachmentSize
SortOnFields.xlsm18.8 KB