vba 升序/降序排序vba excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14858800/
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
sort ascending/descending vba excel
提问by user1987752
I want to sort a column (it's a flagcolumn with Y/N). It should Toggle between ascending / descending on every click.
我想对一列进行排序（它是一个带有 Y/N 的标志列）。它应该在每次点击时在升序/降序之间切换。
my code is not working..I am new to VBA. Any help please.
我的代码不起作用..我是 VBA 的新手。请任何帮助。
Private Sub CommandButton1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row End With
If (Range("E2").Value > Range("E" & CStr(LastRow))) Then
xlSort = xlAscending
Else
xlSort = xlDescending
End If
.Sort Key1:=Range("E2"), Order1:=xlSort, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWorkbook.Save
End Sub
回答by John Bustos
This code worked for me:
这段代码对我有用：
Private Sub CommandButton1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
If (.Range("E2").Value > .Range("E" & CStr(LastRow))) Then
xlSort = xlAscending
Else
xlSort = xlDescending
End If
.Range("E2:E" & LastRow).Sort Key1:=.Range("E2"), Order1:=xlSort, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
ActiveWorkbook.Save
End Sub
Hope this does the trick!!!
希望这能解决问题！！！
回答by David Zemens
This will be easier if you declare a range variable ("rng" in the example below). This code should fix it.
如果你声明一个范围变量（下面例子中的“rng”），这会更容易。此代码应该修复它。
Private Sub CommandButton1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
Dim rng As Range
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
Set rng = Range("E2").Resize(LastRow, 1)
With rng
If (.Cells(1).Value > .Cells(LastRow - 1).Value) Then
xlSort = xlAscending
Else
xlSort = xlDescending
End If
.Sort Key1:=.Cells(1), Order1:=xlSort, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With
ActiveWorkbook.Save
End Sub
回答by Tatsumi
To sort ascending and descending with 2 keys
使用 2 个键进行升序和降序排序
Sub Button1_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
If (.Range("E2").Value > .Range("E" & CStr(LastRow))) Then
xlSort = xlAscending
Else
xlSort = xlDescending
End If
.Range("E2:E" & LastRow).Sort Key1:=.Range("E2"), Order1:=xlSort, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
ActiveWorkbook.Save
End Sub
