excel - 使用宏对表格进行排序

Question

希望这里的这个新手可以在使用宏进行排序方面得到一些帮助。

我有一张要排序的 D:R 表。

首先，整个表需要按照Q列升序排序。

然后同一个表根据 P 列按升序排序。

最后，整个表格需要将每列 D 的黄色突出显示的行带到顶部。

我当前的公式是使用内置记录器记录的，它不起作用。

有人会提供任何建议吗？提前谢谢了!

'Sorting for easier read
    'Check for filter, turn on if none exists
    If Not ActiveSheet.AutoFilterMode Then
        ActiveSheet.Range("D1:R1").AutoFilter
    End If
    'Sort in order of column Q
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add Key:=Range( _
        "Q1:Q10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    'Sort in order of column P
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add Key:=Range( _
        "P1:P10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    'Bring highlighted row to the top
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(4).AutoFilter.Sort.SortFields.Add(Range("D:D" _
        ), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
        , 255, 0)
    With ActiveWorkbook.Worksheets(4).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

Answer 1

这是我对第 52 列的颜色进行排序 yellow :

Sub SortOnDuplicates()

ws.ListObjects("Table1").Range.AutoFilter _
    Field:=52, _
    Criteria1:=RGB(255, 255, 0), _
    Operator:=xlFilterCellColor

End Sub

这是一个升序排序:

ws.ListObjects("Table1").Range.AutoFilter Field:=52
With ws.ListObjects("Table1").Sort
    'Last Name
    .SortFields.Clear
    .SortFields.Add _
        Key:=Range("Table1[[#All],[Last Name]]"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    'First Name
    .SortFields.Clear
    .SortFields.Add _
        Key:=Range("Table1[[#All],[First Name]]"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With