使用 Excel VBA 按列值对工作表数据进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27571923/
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 Worksheet data by column values using Excel VBA
提问by Jesus
I have next userform developed in vba, which takes info from a worksheet for displaying info
我在 vba 中开发了下一个用户表单，它从工作表中获取信息以显示信息
I want to order all the info aphabetically by a Segment, this is the code:
我想按一个段按字母顺序排列所有信息，这是代码：
Function llenarDatosTabla()
Dim vList As Variant
Dim ws As Worksheet: Set ws = Worksheets(BD_PRODXSIST)
ListBox1.Clear
With ws
If (IsEmpty(.Range("AA2").Value) = False) Then
Dim ultimoRenglon As Long: ultimoRenglon = devolverUltimoRenglonDeColumna("A1", BD_PRODXSIST)
vList = ws.Range("AA2:AA" & ultimoRenglon & ":AL2").Value
If IsArray(vList) Then
Me.ListBox1.List = vList
Else
Me.ListBox1.AddItem (vList)
End If
End If
Me.ListBox1.ListIndex = -1
End With
Set vList = Nothing
Set ws = Nothing
End Function
how to make it ordered by 'AD' (SEGMENTO) column???
如何使其按“AD”（SEGMENTO）列排序？？？
回答by Alexander Bell
You can sort your Excel Worksheet in ascending order using VBA statement like the following:
您可以使用 VBA 语句按升序对 Excel 工作表进行排序，如下所示：
Columns("A:XFD").Sort key1:=Range("AD:AD"), order1:=xlAscending, Header:=xlYes
Note: in the column range Columns("A:XFD")instead of XFDenter the last used column pertinent to your case, e.g. Columns("A:DD").
注意：在列范围中，Columns("A:XFD")而不是XFD输入与您的案例相关的最后使用的列，例如Columns("A:DD").
Hope this will help.
希望这会有所帮助。
回答by Reg Edit
To sort a data table, use Excel Namesin conjunction with the CurrentRegionfunction. This is less risky than hard-coding column references and can be done in two simple steps.
要对数据表进行排序，请结合使用Excel 名称和CurrentRegion函数。这比硬编码列引用风险小，可以通过两个简单的步骤完成。
The reason it's preferable to specifying columns is that if you get the columns wrong or they change later, you'll scramble your data! When you perform the sort, the cells in any omitted column(s) will remain where they are, becoming part of the wrong rows. And this is exactly what will happen if you add further columns later, unless you remember to update your VBA.
指定列更可取的原因是，如果列错误或稍后更改，您将打乱数据！执行排序时，任何省略列中的单元格将保留在原处，成为错误行的一部分。如果您稍后添加更多列，这正是会发生的情况，除非您记得更新 VBA。
Here are the two simple steps for using this approach. For this example, I've chosen a data table with four columns and four rows:
以下是使用此方法的两个简单步骤。在这个例子中，我选择了一个四列四行的数据表：
We are going to sort by COL3 descending. The cells in the other three columns share identical values, enabling us to readily verify they all stay with the correct rows.
我们将按 COL3 降序排序。其他三列中的单元格共享相同的值，使我们能够轻松验证它们都保留在正确的行中。
Step 1: choose a cell in the data table that's unlikely to ever be removed, such as the header of a column you intend to make permanent, and define a Name for this cell. You can define the name by selecting the cell and typing directly in Excel's Name dropdown above the worksheet. Here I've used the name RegionTag:
步骤 1：在数据表中选择一个不太可能被删除的单元格，例如您打算永久保留的列的标题，并为该单元格定义一个名称。您可以通过选择单元格并直接在工作表上方的 Excel 名称下拉列表中键入来定义名称。在这里，我使用了名称 RegionTag：
Straight away, CurrentRegion can reference the whole data table just from this. You can see it in action if you code a line of VBA to select the table:
直接，CurrentRegion 可以从这里引用整个数据表。如果您编写一行 VBA 代码来选择表，您可以看到它在运行：
Range("RegionTag").CurrentRegion.Select
This is the result:
这是结果：
That's just for illustration, showing the power of the Name/CurrentRegion combination. We don't need to select the table in order to sort it.
这只是为了说明，展示了 Name/CurrentRegion 组合的威力。我们不需要选择表格来对其进行排序。
Step 2: define a second Name, this time for the column you want to sort by:
第 2 步：定义第二个名称，这次是为您要排序的列：
Make sure the Name refers to the entire column, selected by clicking the column header, rather than just a range of cells in the column.
确保名称是指通过单击列标题选择的整列，而不仅仅是列中的一系列单元格。
That's it! With these two Names defined, we can sort the data table without concerning ourselves with its rows and columns, even if more are added later:
就是这样！定义了这两个 Names 后，我们可以对数据表进行排序，而不必担心它的行和列，即使稍后添加更多：
Range("RegionTag").CurrentRegion.Sort _
key1:=Range("SortCol"), order1:=xlDescending, Header:=xlYes
Here is our data table sorted using the above statement:
这是我们使用上述语句排序的数据表：
