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

Both code blocks are identical and set to sort ascending. xlTopToBottom. — user14797724
– user14797724, Commented Jan 24, 2021 at 18:39
The tag xlTopToBottom doesn't denote the sort order. It's the sub key Order:=xlDescending that determines the sort order. — Andy Galloway
– Andy Galloway, Commented Jan 24, 2021 at 19:09
I'm not clear where your data starts. Your staff names are in row 6 - so do your dates start in row 7 in column C? — user3259118
– user3259118, Commented Jan 24, 2021 at 22:08
Course Names are in column A rows 6 to 11, Trainer Name is in column D rows 6 to 11, Course dates are in column C rows 6 to 11. Frequency of course is in column D rows 6 to 11. Date for next course is in cloumn E rows 6 to 11. Columns F to N will denote whether a particular member of staff is booked on that particular training course with an "X" in the cell denoting the row for that course and the column for that member of staff. I am trying to sort on column C. — Andy Galloway
– Andy Galloway, Commented Jan 25, 2021 at 3:16

Shai Rado · Accepted Answer · 2021-01-24 19:31:38Z

1

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

edited Jan 24, 2021 at 19:31

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

answered Jan 24, 2021 at 19:07

v-c0de

1401 gold badge3 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shai Rado Over a year ago

There's no need to use Call anymore, also you can use lng_SortRow_Start and lng_SortRow_End in the later appearance of Sort

score 0 · Accepted Answer · 2021-01-25 05:53:00Z

0

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

edited Jan 25, 2021 at 5:53

answered Jan 25, 2021 at 5:33

user3259118

2 Comments

Andy Galloway Over a year ago

Thanks for that Kevin. Your solution looks a lot more elegant. I need more of my code because I don't know how mant staff there are going to be in any given company at any given time and the number of courses in the list keeps beeing added to and some of the user organizations will want to purge courses older than 3 years. So I have to count these two variables every time a sort is required. Thanks for your reply. It is appreciated.

user3259118 Over a year ago

Understood Andy - that's why I went with Current.Region - which will automatically expand as you add courses/staff members. Experiment with it and you'll see what I mean. Cheers.

Andy Galloway · Accepted Answer · 2021-01-25 03:43:07Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related