在excel中排序不同大小的表 [英] Sorting a varying size table in excel

问题描述

我想要做的是在电子表格中选择一个表，然后根据两个不同的列排序

What I'm trying to do is select a table in a spreadsheet, and then sort according to 2 different columns

我使用记录宏选项生成了这个代码。表的大小变化是为什么我使用xlDown，不幸的是，代码稍后引用确切的单元格B4：B52。任何想法如何解决这个问题？

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?

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

推荐答案

由于您正在排序 VBA中的（ ListObject ），你可以参考一下。这将动态调整以涵盖整个表列。在此示例中，要排序的列标题/名称为Data1和Data3：

Since you are sorting a Table (ListObject in 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":

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

这篇关于在excel中排序不同大小的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！