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

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

Answer 2

I'm not sure why it is working, but I used some of the idea from v-c0de, and it is working as planned, so thanks for posting. My final code looked like this:

---

Private Sub btnOrder_Click()

Dim varColumnLetter As String Dim varLastRow As Integer Dim xl_SortOrder As XlSortOrder

Application.ScreenUpdating = False

' Set up the sort array
varCourseCount = WorksheetFunction.CountA(Worksheets("External Training Matrix").Range("A6", Worksheets("External Training Matrix").Range("A6").End(xlDown)))
varStaffCount = WorksheetFunction.CountA(Worksheets("External Training Matrix").Range("F5", Worksheets("External Training Matrix").Range("F5").End(xlEnd)))
varColumnLetter = Split(Cells(6, varStaffCount + 5).Address, "$")(1)
varLastRow = varCourseCount + 5

If btnOrder.Caption = "Sort Ascending" Then
    xl_SortOrder = xlAscending
ElseIf btnOrder.Caption = "Sort Descending" Then
    xl_SortOrder = xlDescending
End If

With Worksheets("External Training Matrix").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("C6:C" & varLastRow), _
        SortOn:=xlSortOnValues, _
        Order:=xl_SortOrder, _
        DataOption:=xlSortNormal
    .SetRange Range("A6", varColumnLetter & varLastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
    
If btnOrder.Caption = "Sort Ascending" Then
    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
    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

---

Answer 3

You can try something like this:

Sub SortData(bl_Ascending As Boolean, str_SortColumn As String, lng_SortRow_Start As Long, lng_SortRow_End As Long)

Dim xl_SortOrder As XlSortOrder


If bl_Ascending = True Then
    xl_SortOrder = xlAscending
Else
    xl_SortOrder = xlDescending
End If

With ThisWorkbook.Worksheets("External Training Matrix").Sort
    .SortFields.Clear ' clear previous sorting

    .SortFields.Add Key:=Range(str_SortColumn & lng_SortRow_Start & ":" & str_SortColumn & lng_SortRow_End), _
        SortOn:=xlSortOnValues, _
        Order:=xl_SortOrder, _
        DataOption:=xlSortNormal
    .SetRange Range("A" & lng_SortRow_Start & ":" & "N" & lng_SortRow_End)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

And then call like this:

SortData False, "G", 6, 11

I would suggest you make the rows and data range dynamic. But I'm sure you can figure this out as the next step

I have tested the code with numbers and it works