Sorting a Range based on sheet variable with VBA

Question

Scenario: I have a sheet and am trying to sort a part of it. Inside the sheet I have a dropdown list, which allows me to select the values (aaa, bbb, ccc). Each of those values represent a column and when selected, the code should sort the range by that column.

Problem: The process works for one of the values in the dropdown list, but not for the others (it runs, but nothing happens).

Code:

Sub ratecolumnssort()

    Dim LastRow As Integer
    Dim sortColumn As String, sortAgent As String

    shtMonitoring.Activate
    LastRow = shtMonitoring.Cells(shtMonitoring.rows.count, "B").End(xlUp).row

    sortAgent = shtMonitoring.Cells(8, 9) ' this is where the dropdown with values aaa, bbb and ccc is

    If sortAgent = "aaa" Then
        sortColumn = "F"
    ElseIf sortAgent = "bbb" Then
        sortColumn = "H"
    ElseIf sortAgent = "ccc" Then
        sortColumn = "J"
    End If

    With ActiveSheet.sort
        .SortFields.Add key:=Range(sortColumn & "11"), Order:=xlAscending
        .SetRange Range("B11", "N" & LastRow )
        .Header = xlYes
        .Apply
    End With

End Sub

Question: What am I doing wrong here?

Not tested, but I think that every sorting needs a previous clearing. Try something lke ActiveSheet..AutoFilter.Sort.SortFields.Clear before each sorting? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Nov 8, 2019 at 11:36

SJR · Accepted Answer · 2019-11-08 11:38:48Z

2

You need to clear the sort settings first.

 With ActiveSheet.Sort
        .SortFields.Clear 'add this line
        'as before

answered Nov 8, 2019 at 11:38

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Sorting a Range based on sheet variable with VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related