Excel VBA - Unselect Range after Sort on multiple columns

Question

The code I have applied is as below, purpose being to sort over target worksheet range A:H using A,B,C,D as sort field in that order. The code does the purpose, but after code execution, the screen ends at range A:H being selected. I would want to clear the selection.

As you see I have tried using

wsName.Range("A1").Select
Application.CutCopyMode = False

at the end, but not quite working as I expected.

Public Sub sSortColumn(wsName As Worksheet)
    With wsName.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("A:H")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wsName.Sort.SortFields.Clear
    wsName.Range("A1").Select
    Application.CutCopyMode = False
End Sub`

A better question would be "how do I avoid selecting in the first place". To answer that, [start here](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Feb 28 '16 at 23:50
Thanks I did not realize that Range("A:H") actually does the selection, I had thought it is the .apply . I am out of town at the moment but will try this later tomorrow. Cheers. — Yitian Jing Michael, Feb 29 '16 at 10:36

score 1 · Accepted Answer · edited May 23 '17 at 12:22

There is a more succinct coding syntax to a Range.Sort method. However, it is limited to three keys at a time. The solution is to sort on the fourth key first, then the three remaining keys in the order that you prefer.

The parent worksheet of your SortFields Key(s) are defaulted to the ActiveSheet property. There is no explicit relationship to the Worksheet Object passed into the sub procedure.

Sub main()
    'wsName worried me so I'm making sure to
    'pass in a correct worksheet object reference
    sSortColumn Worksheets("Sheet1")
End Sub

Public Sub sSortColumn(wsName As Worksheet)
    With wsName
        .Sort.SortFields.Clear    '<~~reset the worksheet's .SortFields
        With .Cells(1, 1).CurrentRegion
            With .Resize(.Rows.Count, 8) '<~~ columns A:H originating at A1
                .Cells.Sort Key1:=.Columns(4), Order1:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlYes   '<~~sort the fourth column first
                .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                            Key2:=.Columns(2), Order2:=xlAscending, _
                            Key3:=.Columns(3), Order3:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlYes   '<~~sort the remaining three columns
            End With
        End With
        'if you want to leave this worksheet's selection on A1 then
        'first make sure that the worksheet is active and then
        'select the cell
        .Activate
        .Range("A1").Select
        'the above is really not necessary. The original selection on the worksheet was
        'not changed
    End With
End Sub

There was similarly no guarantee that the worksheet object passed into the sSortColumn sub procedure was the ActiveSheet. You cannot use the Range .Select method¹ on a cell that is not on the ActiveSheet.

_{You might be interested in the following the Q&A on the Application.CutCopyMode property: Should I turn .CutCopyMode back on before exiting my sub procedure?}

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Thanks - it works like a charm. Interestingly to know that range.sort have a limit of three keys and (I feel) works quicker than worksheet.sort — Yitian Jing Michael, Mar 01 '16 at 11:30

Excel VBA - Unselect Range after Sort on multiple columns

1 Answers1