title: Trying to sort a worksheet range using VBA - ascending works but not descending
body: <p>I have a range of training courses in a worksheet from &quot;A6&quot; to &quot;E11&quot;. The staff to attend the courses are listed in columns F to N, with their names in row 6 and their attendance indicated in the relevant cell by an &quot;X&quot;. The course dates are in column C. I have a button called <code>btnOrder</code> attached to the worksheet. The caption of the button toggles from &quot;Sort Ascending&quot; to &quot;Sort Descending&quot;. I am using the following code. The code works fine to sort &quot;Ascending&quot;, but regardless of which part of the code runs, the data is always sorted &quot;Ascending&quot;. Can anyone see what I'm doing wrong?</p>
  <pre class="lang-vb prettyprint-override"><code>Private Sub btnOrder_Click()
      Dim varColumnLetter As String
      Dim varLastRow As Integer
  
      Application.ScreenUpdating = False
      
      If btnOrder.Caption = &quot;Sort Ascending&quot; Then
          With Worksheets(&quot;External Training Matrix&quot;).Sort
              .SortFields.Clear
              .SortFields.Add Key:=Range(&quot;C6:C11&quot;), _
                  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
              .SetRange Range(&quot;A6:N11&quot;)
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
          End With
          
          MsgBox &quot;Courses sorted successfully into ascending order by course date, oldest courses at the top&quot;, vbOKOnly + vbInformation, &quot;Success&quot;
          btnOrder.Caption = &quot;Sort Descending&quot;
          
      ElseIf btnOrder.Caption = &quot;Sort Descending&quot; Then
          With Worksheets(&quot;External Training Matrix&quot;).Sort
              .SortFields.Add Key:=Range(&quot;C6:C11&quot;), _
                  SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
              .SetRange Range(&quot;A6:N11&quot;)
              .Header = xlNo
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
          End With
      
          MsgBox &quot;Courses sorted successfully into descending order by course date, newest courses at the top&quot;, vbOKOnly + vbInformation, &quot;Success&quot;
          btnOrder.Caption = &quot;Sort Ascending&quot;
      End If
      
      ActiveSheet.Range(&quot;A6&quot;).Select
      
      Application.ScreenUpdating = True
  End Sub
  </code></pre>