EXCEL VBA 大量字段排序

下面的代码执行大量的排序，
排序范围为第一列开始，到指定的列为止（最多可以到BL列（也可能是BM列））。
需要配合下面的ColNameToColNumber函数使用

Option Explicit
Sub SortCode_AllTitle()
    Dim ws As Worksheet
    Dim num As Integer
    Dim i As Integer
    
    '执行对象表的设定
    Set ws = ThisWorkbook.Worksheets("工作表名")

    '设置参与排序的最终的列名
    num = ColNameToColNumber("Z")
    With ws
        With .Sort.SortFields
            .Clear '清除排序条件
            For i = 1 To num
                DoEvents
                Debug.Print ws.Cells(1, i)
                .Add Key:=ws.Cells(1, i), Order:=xlAscending
            Next i
        End With
        '执行排序
        .Sort.SetRange .Range("A1:Z100")
        .Sort.Header = xlYes
        .Sort.Apply
    End With

End Sub

下面的函数执行指定列名的排序
将需要参与排序的列设置到arrs里面即可

Sub SortCode_SetTitle()
    Dim ws As Worksheet
    Dim num As Integer
    Dim i As Integer
    Dim arrs As Variant
    Dim arr As Variant
    
    '设定工作表
    Set ws = ThisWorkbook.Worksheets("工作表名")

    '设置参与排序的列的名称
    arrs = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O")
    With ws
        With .Sort.SortFields
            .Clear '清除排序条件
            For Each arr In arrs
                DoEvents
                Debug.Print arr
                Debug.Print ws.Cells(1, arr)
                .Add Key:=ws.Cells(1, arr), Order:=xlAscending
            Next arr
        End With
        '执行排序
        .Sort.SetRange .Range("A1:Z100")
        .Sort.Header = xlYes
        .Sort.Apply
    End With

End Sub

下面的代码能够将列名转换成数字，供计数使用

'将列名转换成数字
Function ColNameToColNumber(TargetColumn As String) As Integer
    Dim Result As Integer
    Result = Cells(1, TargetColumn).Column
    Debug.Print "Function : ColNameToColNumber return is " & Result
    ColNameToColNumber = Result
End Function