vba 如何从A到Z对列中的单元格进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22259045/
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
How to sort cells in a column from A to Z?
提问by Petrik
The code is from slightly edited macro. I tried to remove the 'messy code', however it is not working. The aim of it is to sort data in column BF from A to Z.
代码来自稍微编辑过的宏。我试图删除“凌乱的代码”，但它不起作用。它的目的是将 BF 列中的数据从 A 到 Z 排序。
Dim InSheet As Worksheet
Set InSheet = ThisWorkbook.Worksheets("A to Z")
Dim LastRow as Integer
LastRow = InSheet.Cells(Rows.Count, 58).End(xlUp).Row
InSheet.Select
Columns("BF:BF").Select
InSheet.Sort.SortFields.Clear
InSheet.Sort.SortFields.Add Key:=Range( _
"BF1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With InSheet.Sort
.SetRange Range("A1:BF" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
SO I tried this and it is not working:
所以我试过这个，但它不起作用：
Dim InSheet As Worksheet
Set InSheet = ThisWorkbook.Worksheets("A to Z")
Dim LastRow as Integer
LastRow = InSheet.Cells(Rows.Count, 58).End(xlUp).Row
InSheet.Columns("BF:BF")
InSheet.Sort.SortFields.Clear
InSheet.Sort.SortFields.Add Key:=Range( _
"BF1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With InSheet.Sort
.SetRange Range("A1:BF" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Any ideas? I would like to really avoid .selectan other stuff as it is hugely decreasing performance.
有任何想法吗？我想真正避免.select其他东西，因为它会大大降低性能。
回答by Steven Martin
You can just directly sort the Range with a Key with out the rest of the code you have.
您可以直接使用 Key 对 Range 进行排序，而无需使用您拥有的其余代码。
Range("A1:BF" & LastRow).SORT Key1:=Range("BF1"), Order1:=xlAscending_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal
回答by Petrik
I used advice provided by both Steven Martin's answer and Dmitry Pavliv's comment, and this is working pretty well:
我使用了 Steven Martin 的回答和 Dmitry Pavliv 的评论提供的建议，并且效果很好：
Dim InSheet As Worksheet
Set InSheet = ThisWorkbook.Worksheets("A to Z")
Dim LastRow As Integer
LastRow = InSheet.Cells(Rows.Count, 58).End(xlUp).Row
With InSheet.Sort ' sort data from A to Z
.SetRange InSheet.Range("A1:BF" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
