在 VBA 中对变化的范围使用排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22174908/
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
Using Sort in VBA for a Range that Changes
提问by aCarella
I have a range of cells in VBA that changes each time the code is run. I am trying to write code so that this range is sorted by column F.
我在 VBA 中有一系列单元格，每次运行代码时都会发生变化。我正在尝试编写代码，以便此范围按 F 列排序。
The problem I am having is that it can only be this specific range of cells. There are other cells underneath this range that I do not want sorted, and this range changes in size. Part of the code is below. This is what I have tried so far with no luck.
我遇到的问题是它只能是这个特定范围的单元格。在此范围下还有其他我不想排序的单元格，并且此范围的大小会发生变化。部分代码如下。这是我迄今为止尝试过但没有运气的方法。
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
vtools = Selection
ActiveWorkbook.Worksheets("Exceptions Weekly Summary").Sort.SortFields.Add Key _
:=Range(vtools), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Exceptions Weekly Summary").Sort
.SetRange Range("B11:H14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
This does not work.
这不起作用。
I cannot directly reference the cells (I cannot use Range("F2:F5") for example) because these cells are in different places each time the code is run. I know how to find what I need sorted, and even select it, but I am having trouble telling the sort function which column to sort by.
我不能直接引用这些单元格（例如，我不能使用 Range("F2:F5")），因为每次运行代码时这些单元格都位于不同的位置。我知道如何找到我需要排序的内容，甚至选择它，但是我无法告诉排序函数要根据哪一列排序。
Can someone help me with this? Thank you so much in advance!
有人可以帮我弄这个吗？非常感谢您！
回答by Graffl
If I understood correctly this will help. It finds out the row numbers of the selected area and then makes a range in column F with these numbers and uses this as the key for ordering.
如果我理解正确，这将有所帮助。它找出所选区域的行号，然后用这些数字在 F 列中创建一个范围，并以此作为排序的关键。
Sub sortOnlySelectedArea()
Dim actSheet As Worksheet
Dim upper, lower As Integer
Dim tempString As String
Dim selectedArea As Range
Set actSheet = Application.Worksheets("Sheet1")
' here you have to put in your part to make the right selection
actSheet.Range("E5:G6").Select
Set selectedArea = Selection
upper = selectedArea.Row
lower = upper + selectedArea.Rows.Count - 1
tempString = "F" & CStr(upper) & ":F" & CStr(lower)
actSheet.Sort.SortFields.Clear
actSheet.Sort.SortFields.Add Key:=Range(tempString), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With actSheet.Sort
.SetRange selectedArea
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
