VBA Excel 对多列进行排序

VBA Excel sorting on multiple columns

提问by Antoon

I have an Excel table, where I'd like that all rows (except the header row) in Sheet "CR" that have a value in it (excluding formulas if possible (column A contains formulas)) are sorted first by column B (name = TEAM), then C (name = BUILDING) and finally D (name = DATE_MAJ) before the file is saved.

我有一个 Excel 表格，我希望表格“CR”中的所有行（标题行除外）中有一个值（如果可能，不包括公式（A 列包含公式））首先按 B 列排序（ name = TEAM），然后是 C（name = BUILDING），最后是 D（name = DATE_MAJ），然后保存文件。

I'm an absolute noob with VBA, so I'm trying out stuff that I find left and right on the fora and modify it to my needs. From searching around, I tried this code in the Excel VBA Object 'Workbook', but it gives an error:

我是 VBA 的绝对菜鸟，所以我正在尝试我在论坛上找到的东西，并根据我的需要进行修改。通过四处搜索，我在 Excel VBA 对象“工作簿”中尝试了此代码，但它给出了错误：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Setup column names
Col1name = "SECTION"
Col2name = "BATIMENT"
Col3name = "DATE_MAJ"

'Find cols
For Each cell In Range("A1:" & Range("A1").End(xlToRight).Address)
    If cell.Value = Col1name Then
        Col1 = cell.Column
    End If
    If cell.Value = Col2name Then
        Col2 = cell.Column
    End If
    If cell.Value = Col3name Then
        Col3 = cell.Column
    End If

Next

'Below two line:- if they are blank e.g. column not found it will error so a small bit of error handling 
If Col1 = "" Then Exit Sub
If Col2 = "" Then Exit Sub
If Col3 = "" Then Exit Sub

'Find last row - dynamic part
lastrow = ActiveSheet.Range("A100000").End(xlUp).Row

'Convert col numer to name
Col1 = Split(Cells(1, Col1).Address(True, False), "$")
Col2 = Split(Cells(1, Col2).Address(True, False), "$")
Col3 = Split(Cells(1, Col3).Address(True, False), "$")

'Sort
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(Col1(0) & "2:" & Col1(0) & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range(Col2(0) & "2:" & Col2(0) & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range(Col3(0) & "2:" & Col3(0) & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("A1:K" & lastrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

I'd be grateful for any help in getting the code right. Below is a link to the Excel file (I took out the above code as it didn't work).

如果您能帮助我正确编写代码，我将不胜感激。下面是 Excel 文件的链接（我取出了上面的代码，因为它不起作用）。

Dropbox link to Excel file

Dropbox 链接到 Excel 文件

回答by user3598756

Since you only have three sorting columns you may want to use Sort()method of Rangeobject, instead of the namesake method of Worksheetobject

既然你只有三个排序列，你可能要使用Sort()的方法Range的对象，而不是同名的方法Worksheet对象

Furthermore assuming columns headers as per linked excel files you could try this:

此外，假设根据链接的 excel 文件列标题，您可以尝试以下操作：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim col1 As Range, col2 As Range, col3 As Range
    Dim lastRow As Long

    'Setup column names
    Const col1Name As String = "SECTION"
    Const col2Name As String = "BUILDING" '"BATIMENT"
    Const col3Name As String = "DATE UPDATE" '"DATE_MAJ"

    With Worksheets("CR") '<--| reference your worksheet
        'Find last row - dynamic part
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).row ' <--|find its column "A" last not empty row index
        'Find cols
        With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) '<--|reference its row 1 cells from column 1 to last not empty one and search for sorting columns whose header matches above set column names
            If Not TryGetColumnIndex(.Cells, col1Name, col1) Then Exit Sub '<--| if 1st sorting column not found then exit sub
            If Not TryGetColumnIndex(.Cells, col2Name, col2) Then Exit Sub '<--| if 2nd sorting column not found then exit sub
            If Not TryGetColumnIndex(.Cells, col3Name, col3) Then Exit Sub '<--| if 3rd sorting column not found then exit sub
            .Resize(lastRow).Sort _
                            key1:=col1, order1:=xlAscending, DataOption1:=xlSortNormal, _
                            key2:=col2, order2:=xlAscending, DataOption2:=xlSortNormal, _
                            key3:=col3, order3:=xlAscending, DataOption3:=xlSortNormal, _
                            Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
        End With
    End With
End Sub

Function TryGetColumnIndex(rng As Range, colName As String, col As Range) As Boolean
    Set col = rng.Find(What:=colName, LookIn:=xlValues, LookAt:=xlWhole)
    TryGetColumnIndex = Not col Is Nothing
End Function