excel - Trying to sort a worksheet range using VBA - ascending works but not descending

Question

I have a range of training courses in a worksheet from "A6" to "E11". 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 "X". The course dates are in column C. I have a button called btnOrder attached to the worksheet. The caption of the button toggles from "Sort Ascending" to "Sort Descending". I am using the following code. The code works fine to sort "Ascending", but regardless of which part of the code runs, the data is always sorted "Ascending". Can anyone see what I'm doing wrong?

Private Sub btnOrder_Click()
    Dim varColumnLetter As String
    Dim varLastRow As Integer

    Application.ScreenUpdating = False
    
    If btnOrder.Caption = "Sort Ascending" Then
        With Worksheets("External Training Matrix").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("C6:C11"), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A6:N11")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        MsgBox "Courses sorted successfully into ascending order by course date, oldest courses at the top", vbOKOnly + vbInformation, "Success"
        btnOrder.Caption = "Sort Descending"
        
    ElseIf btnOrder.Caption = "Sort Descending" Then
        With Worksheets("External Training Matrix").Sort
            .SortFields.Add Key:=Range("C6:C11"), _
                SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .SetRange Range("A6:N11")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
        MsgBox "Courses sorted successfully into descending order by course date, newest courses at the top", vbOKOnly + vbInformation, "Success"
        btnOrder.Caption = "Sort Ascending"
    End If
    
    ActiveSheet.Range("A6").Select
    
    Application.ScreenUpdating = True
End Sub

question from:https://stackoverflow.com/questions/65874415/trying-to-sort-a-worksheet-range-using-vba-ascending-works-but-not-descending

Answer 1

Based on your description of the data, you could achieve the same result with slightly less code. Please try the following:

Option Explicit
Private Sub btnOrder_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("External Training Matrix")

If btnOrder.Caption = "Sort Ascending" Then
    ws.Cells(6, 1).CurrentRegion.Sort _
    Key1:=ws.Range("C6"), order1:=xlDescending, Header:=xlYes
    MsgBox "Courses sorted Descending...etc."
    btnOrder.Caption = "Sort Descending"
        Else
        ws.Cells(6, 1).CurrentRegion.Sort _
        Key1:=ws.Range("C6"), order1:=xlAscending, Header:=xlYes
        MsgBox "Courses sorted Ascending...etc."
        btnOrder.Caption = "Sort Ascending"
End If

ws.Sort.SortFields.Clear

End Sub