按升序/降序排列vba excel

问题描述：

我想排序列（它是带Y/N的标记列）。它应该在每次点击上升/下降之间切换。按升序/降序排列vba excel

我的代码不工作..我是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

你缺少一个'结束With'将其排序升序和降序之前'ActiveWorkbook.Save' – 2013-02-13 17:03:38

右我添加了结尾。现在它显示对象不支持此属性或方法运行时错误 - 438请咨询 – user1987752 2013-02-13 17:13:07

您现在有2个'End With's - 删除'LastRow ='行中的一个，并且在任何地方看到单词'Range'，替换它与'.Range' – 2013-02-13 17:16:40

答

此代码为我工作：

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

希望这会做的伎俩！

我修改为您的代码。现在它显示范围类的排序方法失败。 – user1987752 2013-02-13 17:58:27

尝试只是复制和粘贴我的代码确切地说 - 我在我的工作簿中运行了它，它工作... – 2013-02-13 18:01:24

它工作了！非常感谢！！ – user1987752 2013-02-13 18:15:21

答

如果（在示例中“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

当我运行代码时，它显示“范围类的排序方法失败” – user1987752 2013-02-13 18:03:44

您使用的是什么版本的Excel？ – 2013-02-13 18:05:18

答

要使用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

按升序/降序排列vba excel

相关推荐