sort filed

最新推荐文章于 2021-12-21 14:21:03 发布

转载最新推荐文章于 2021-12-21 14:21:03 发布 · 441 阅读

本文介绍两个使用VBA编写的Excel宏，用于实现自定义的数据排序功能。sort1宏通过创建自定义列表并按升序对指定范围内的数据进行排序；sort2宏则使用自定义列表对数据进行降序排序，并在完成后删除该自定义列表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Option Explicit

Sub sort1()
'
' sort1 宏
'

'
    Dim arr1, str1, i
    
    arr1 = Array("安装地址", "电话号码", "安装套餐", "归属人", "安装日期")
    For Each i In arr1
    str1 = str1 & i & ","
    Next
    str1 = Left(str1, Len(str1) - 1)
    Debug.Print str1
    
    
    Application.DeleteCustomList ListNum:=12
    Application.AddCustomList ListArray:=arr1
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C3:C7") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "安装地址,电话号码,安装套餐,归属人,安装日期", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C2:BT7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub sort2()
'
' sort2 宏
'

'
    Dim strin As Variant, arr, n

    strin = "安装套餐,电话号码,安装地址,归属人,安装日期"
    arr = Array("安装地址", "电话号码", "安装套餐", "归属人", "安装日期")
    Application.AddCustomList ListArray:=arr
    n = Application.GetCustomListNum(arr)
    Debug.Print n

    Range("C2").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C3:C7") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:= _
        Application.CustomListCount, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C2:BT7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.DeleteCustomList n
End Sub