Excel 2016中按多个条件的Excel VBA多行排序 [英] Excel VBA Multi line sort by multiple criteria in Excel 2016

问题描述

从该线程继续:我想基于多个条件对列表进行排序，然后对其进行排名，最后将其显示在下拉数据验证列表中(下拉菜单在上面的链接中引用的线程中进行了介绍).

I want to sort a list based on multiple criteria and then rank it and finally display it in a drop down data validation list (drop downs are covered in the thread referenced in the link above).

如何在Excel 2016中对多个critera上的数据进行排序?我尝试使用高级过滤器和worksheetChange事件.我想先对数据进行排序，然后再对数据进行排序.

How can I sort data on multiple critera in Excel 2016? I have tried using the advanced filter and the worksheetChange event. I want to manipulate the data before I sort it and I want to rank the data before I sort it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$2" Then
    Range("ÄB1[#All]").AdvancedFilter Action:=xlFilterInPlace, _
                       CriteriaRange:=Range("D1:D2"), Unique:=False
End If

End Sub

推荐答案

令人困惑的是您正在选择排序中包含的单元格.

Confusing that you are selecting a cell that is included in the sort.

这将基于列A作为键(在A上排序)过滤一系列数据(A1:D15). 如果可以，则按单个键对范围进行排序.

This will filter a range of data (A1:D15) based on Column A as the key (sorts on A). A range sorted on a single key if you will.

Sub sortbyColumnA()
Dim ws As Worksheet
Set ws = Sheets("Sheet1") 'Name your worksheet right here

    If ws.AutoFilterMode = False Then ws.Range("A1:D15").AutoFilter
    ws.AutoFilter.Sort.SortFields.Clear
    ws.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("A1:D15").AutoFilter
End Sub

这里是一个多键排序的示例，您当然需要第一个排序的倍数才能使第二个排序键生效，而第二个排序的倍数则需要相同的第三个排序键.您可以根据需要对任意多个键进行排序，具体取决于数据集的大小. 它按排名第一-按名字第二-按分数第三(升序可能要降级-提示提示)

Here is an example of a multi key sort of course you will need multiples in the first sort for the second sort key to be effective, and the same for multiples in the second sort for the third sort key. You can sort on as many keys as you wish, depends on how big the data set is. Its sorts first by Rank - second by first name - third by points (ascending maybe you want descending here - hint hint)

Sub sortbyMultiColumn()

    Dim ws As Worksheet
    Set ws = Sheets("Sheet1") 'Name your worksheet right here

        If ws.AutoFilterMode = False Then ws.Range("A1:D33").AutoFilter
        ws.AutoFilter.Sort.SortFields.Clear
        'First Sort
        ws.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'Second Sort
        ws.AutoFilter.Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'Third Sort
        ws.AutoFilter.Sort.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        With ws.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        ws.Range("A1:D33").AutoFilter

    End Sub

这篇关于Excel 2016中按多个条件的Excel VBA多行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！