用于按列名、不同位置对多列进行排序的 VBA 代码

VBA code to sort multiple columns by column name, differing locations

提问by newtoVBA

I am new to VBA coding and would like a VBA script that sorts multiple columns. I first sort column F from smallest to largest, and then sort column K. However, I would like the Range value to be dynamic based on the column name rather than location (i.e. the value in column F is called "Name", but "Name" won't always be in column F)

我是 VBA 编码的新手，想要一个对多列进行排序的 VBA 脚本。我首先从最小到最大对列 F 进行排序，然后对列 K 进行排序。但是，我希望范围值是基于列名而不是位置的动态值（即 F 列中的值称为“名称”，但是“名称”不会总是在 F 列中）

I'm looking to change all of the Range values in the macro, and am thinking of replacing it with a FIND function, am I on the right track?

我想更改宏中的所有 Range 值，并考虑用 FIND 函数替换它，我是否在正确的轨道上？

I.e. Change Range _ ("F1:F10695")

即更改范围_（“F1：F10695”）

to something like Range (Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range(Selection, Selection.End(xlDown)).Select

像 Range (Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range(Selection, Selection.End(xlDown)).Select

I have also seen some VBA script templates that use the Dim and Set functions to create lists, i.e. set x="Name", then sort for X in the matrix. Is that a better approach? Thank you for your help, I've attached the basic VBA script template below

我还看到了一些 VBA 脚本模板，它们使用 Dim 和 Set 函数来创建列表，即设置 x="Name"，然后对矩阵中的 X 进行排序。这是更好的方法吗？感谢您的帮助，我在下面附上了基本的 VBA 脚本模板

Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.AutoFilter
    Range("F1").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("F1:F10695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("K1").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("K1:K10695"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

采纳答案by Dmitry Pavliv

UPD:

更新：

Try this one:

试试这个：

Sub test()

    Dim rngName As Range
    Dim rngDate As Range
    Dim emptyDates As Range

    Dim ws As Worksheet
    Dim lastrow As Long


    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        Set rngName = .Range("1:1").Find(What:="Name", MatchCase:=False)
        Set rngDate = .Range("1:1").Find(What:="Date", MatchCase:=False)

        If Not rngName Is Nothing Then
            lastrow = .Cells(.Rows.Count, rngName.Column).End(xlUp).Row
            On Error Resume Next
            Set emptyDates = .Range(rngDate, .Cells(lastrow, rngDate.Column)).SpecialCells(xlCellTypeBlanks)
            On Error GoTo 0
            If Not emptyDates Is Nothing Then
                emptyDates.EntireRow.Delete
            End If
        End If

        With .Sort
            .SortFields.Clear
            If Not rngName Is Nothing Then
                .SortFields.Add Key:=rngName, _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End If
            If Not rngDate Is Nothing Then
                .SortFields.Add Key:=rngDate, _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End If
            .SetRange ws.Cells
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

End Sub

Notes:

笔记：

1. change Sheet1in line ThisWorkbook.Worksheets("Sheet1")to the sheet name that is true for you
2. code tries to find "Name" and "Date" in first row, and then, if this items found, adds SortFields, corresponding to that columns
3. as follow up from comments, OP wants also to delete rows with empty dates

1. 改变Sheet1在线路ThisWorkbook.Worksheets("Sheet1")表名称，对你是真心的
2. 代码尝试在第一行中查找“名称”和“日期”，然后，如果找到此项目，则添加SortFields, 对应于该列
3. 作为评论的后续行动，OP 还希望删除日期为空的行

回答by user8332007

Follow this tested code for 7 columns with dinam last cell. By RCC66

按照此测试代码对 7 列使用 dinam 最后一个单元格进行操作。通过 RCC66

Sub Auto_Open()

' Order by
    Dim LastRow As Integer

    With ActiveSheet
        intLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "Q3:Q" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "L3:L" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "O3:O" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "J3:J" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "B3:B" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "H3:H" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Invoices").Sort.SortFields.Add Key:=Range( _
        "E3:e" & intLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

    With ActiveWorkbook.Worksheets("Invoices").Sort
        .SetRange Range("A1:R" & intLastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub