这是在Excel中对表的排序进行编码的最干净的方法吗? [英] Is this the cleanest way to code the sorting of a table in Excel?

问题描述

这是我编写的用于基于两列对表进行排序的代码，然后在完成后取消选择该表.有没有更干净的方式编写此代码?

Here's the code I've written to sort a table based on two columns then deselect the table once that's done. Is there a cleaner way to code this?

Sub SortTable()

    ' Sorts table

    Worksheets("Data").ListObjects("Table").Sort.SortFields. _
        Clear
    Worksheets("Data").ListObjects("Table").Sort.SortFields. _
        Add Key:=Range("Table[Date]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    Worksheets("Data").ListObjects("Table").Sort.SortFields. _
        Add Key:=Range("Table[Info]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With Worksheets("Data").ListObjects("Table").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' Clears table selection

    With Worksheets("Data")
        .Activate
        .Range("A1").Select
    End With

End Sub

推荐答案

如果您知道要排序的列的位置，则此方法似乎可以解决问题.

If you know the position of the columns to sort, then this method seems to clean things up.

With Sheets("data").ListObjects("Table")
    .Range.Sort Key1:=.ListColumns(2), Order1:=xlAscending, _
                Key2:=.ListColumns(4), Order2:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    .Parent.Range("A1").Select
End With

对于上述情况，日期"列表列为2，信息"为4.如果您不知道列序号，则需要引用父级工作表中的列.

For the above, the Date list column is 2 and Info is 4. If you don't know the column ordinals then you need to reference the columns from the parent worksheet.

With Sheets("data").ListObjects("Table")
    .Range.Sort Key1:=.Parent.Range("Table[Date]"), Order1:=xlAscending, _
                Key2:=.Parent.Range("Table[Info]"), Order2:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    .Parent.Range("A1").Select
End With

这篇关于这是在Excel中对表的排序进行编码的最干净的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！