Sorting by Text Value in VBA

Question

After running a VBA function on my spreadsheet, I would like to re-sort the data automatically, so the user doesn't have to. The sort options I'm trying to complete are as follows:

And my vba code is currently:

Columns("A:AA").Sort key1:=Range("C1:C1"), order1:=xlAscending, key2:=Range("G1:G1"), order2:=xlAscending, key3:=Range("A1:A1"), order3:=xlAscending, Header:=xlYes

As you can see, column A will sort properly, although the other 2 will not sort based on the Fail, Dismissed, Passed criteria and the Critical, High, Medium, Low

Is what I'm trying to do possible? Does anyone know a way to specify these Sort Orders?

Have you used the Macro Recorder to see what code is generated if you do that sort manually? — YowE3K
– YowE3K, Commented Aug 31, 2016 at 19:57
Yea Scott, the question you linked to answers my question within his question :P (although I don't think it's a duplicate) — Tyler Dickson
– Tyler Dickson, Commented Aug 31, 2016 at 20:24

YowE3K · Accepted Answer · 2016-08-31 20:10:13Z

Using the macro recorder led me to the following code:

With ActiveSheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("C:C"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending, _
                         CustomOrder:="Fail,Dismissed,Passed", _
                         DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("G:G"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending, _
                         CustomOrder:="Critical,High,Medium,Low", _
                         DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=.Range("A:A"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending, _
                         DataOption:=xlSortNormal
    .Sort.SetRange .Range("A:AA")
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
End With

Hopefully that should do what you want.

Collectives™ on Stack Overflow

Sorting by Text Value in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related