vba 在excel中对不同大小的表格进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20053802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sorting a varying size table in excel
提问by Chris
What I'm trying to do is select a table in a spreadsheet, and then sort according to 2 different columns
我想要做的是在电子表格中选择一个表格，然后根据 2 个不同的列进行排序
I generated this code with the record macro option. The table changes in size which is why I have used the xlDown, unfortunately the code later references the exact cells "B4:B52". Any idea how I might solve this issue?
我使用记录宏选项生成了此代码。表格的大小会发生变化，这就是我使用 xlDown 的原因，不幸的是，代码后来引用了确切的单元格“B4:B52”。知道我如何解决这个问题吗？
Range("B4:J4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
"B4:B52"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
"G4:G52"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B4:J52")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
回答by Doug Glancy
Since you are sorting a Table(ListObjectin VBA), you'll want to refer to it. This will dynamically adjust to encompass entire table columns. In this example the column headers/names to be sorted are "Data1" and "Data3":
由于您在 VBA中对Table( ListObject)进行排序，因此您需要引用它。这将动态调整以包含整个表列。在本例中，要排序的列标题/名称是“Data1”和“Data3”：
Sub SortTable()
Dim lo As Excel.ListObject
'change this assignment to suit your table location and name
Set lo = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
With lo
.Sort.SortFields.Clear
.Sort.SortFields.Add _
Key:=Range("Table1[data1]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add _
Key:=Range("Table1[data3]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
回答by Petrushchak Bohdan
I would sort the column in the table in the following way:
我将按以下方式对表中的列进行排序：
Sub SortingTable()
Dim ws As Worksheet
set ws = ActiveSheet
Dim target_table As ListObject
Set target_table = ws.ListObjects(wsName)
Dim sort_column_index As Long
sort_column_index = target_table.ListColumns("ColumnToBeSortedName").Index
Dim sort_column As Range
Set sort_column = target_table.ListColumns(sort_column_index).Range
' Applying the sorting to the table
With target_table.Sort
.SortFields.Clear
.SortFields.Add Key:=sort_column _
, SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.Apply
End With
End Sub
In fact, the only difference is that you declare a Range and assign it to the specific table column that should be sorted. Doing it this way, you're able to apply the macro to tables on different sheets assuming that the tables contain the column with name "ColumnToBeSortedName".
实际上，唯一的区别是您声明了一个 Range 并将其分配给应排序的特定表列。通过这种方式，您可以将宏应用于不同工作表上的表，假设这些表包含名为“ColumnToBeSortedName”的列。
