vba 命名然后对动态范围进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27507183/
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
Name and then sort a dynamic range
提问by aoswald
I have a range that length keeps changing. I need to redefine the named range each time it changes and then sort it.
我有一个长度不断变化的范围。我需要在每次更改时重新定义命名范围，然后对其进行排序。
I have this so far:
到目前为止我有这个：
Sub Macro2()
'
' Name and Sort
'
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="data4", RefersToR1C1:= _
"='Data Storage'!R3C1:R25C18"
ActiveWorkbook.Names("data4").Comment = ""
Application.Goto Reference:="data4"
ActiveWorkbook.Worksheets("Data Storage").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data Storage").sort.SortFields.Add Key:=Range( _
"D3:D25"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data Storage").sort
.SetRange Range("A3:R25")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
But the next time the range changes it only sorts on the previous range. I think it has to do with the R3C1:R25C18 reference but I don't know how to change that each time the range changes.
但是下次范围更改时，它只会对前一个范围进行排序。我认为它与 R3C1:R25C18 参考有关，但我不知道每次范围改变时如何改变它。
Thanks for any help.
谢谢你的帮助。
采纳答案by bilbo_strikes_back
This code checks for the last row with data, names the range, and sorts the named range
此代码检查带有数据的最后一行，命名范围，并对命名范围进行排序
Dim lngRowLast As Long
'Find last row
lngRowLast = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Your range goes here
Range(cells(3,1),cells(lngRowLast,18)).Name = "data4"
Range("data4").Sort Key1:=Cells(3, 1), Order1:=xlAscending, _
Header:=xlYes
