sort ascending/descending vba excel

1
votes

I want to sort a column (it's a flagcolumn with Y/N). It should Toggle between ascending / descending on every click.

my code is not working..I am new to VBA. Any help please.

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
3
excelvba
You are missing an End With Place it right before ActiveWorkbook.Save – Scott Holtzman
I added the end with. Now it shows object does not support this property or method run time error - 438 please advice – user1987752
You now have 2 End Withs - Remove the one on the LastRow = line and everywhere you see the word Range, replace it with .Range – John Bustos
good catch @JohnBustos -> I didn't see that in my edit. The code was copied in a bizarre way. – Scott Holtzman
Still it shows Application defined or object defined runtime error – user1987752

3 Answers

6
votes

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!!!

0
votes

This will be easier if you declare a range variable ("rng" in the example below). This code should fix it.

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
-1
votes

To sort ascending and descending with 2 keys

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