CODEWITHSUNDEEP
excelvbasorting
user3702207
user3702207

Reputation: 13

VBA Sort in Excel leaves table contents selected

I have an Excel spreadsheet that hold a leaderboard table on one sheet based on the data in a separate sheet. I monitor a change on the data sheet and then automatically sort the leaderboard table. Everything seems to work fine but the contents of the leaderboard table are left selected after my sort. How do I clear the selection seamlessly without affecting any selection on the data sheet.

I've even been a good boy and done the sort using With instead of Select command.

Here is what I have:

Sheet2(Code) - Data sheet

Sub Worksheet_Change(ByVal target As Range)

' Check if the item changed is in group area
If Not Intersect(target, target.Worksheet.Range("F7:H93")) Is Nothing Then
  ActiveWorkbook.Worksheets("Leaderboard").SortLeaderboard
End If
End Sub

Sheet1(Code) - Leaderboard sheet

Sub SortLeaderboard()
Dim lo As Excel.ListObject
Set lo = ActiveWorkbook.Worksheets("Leaderboard").ListObjects("Table3")

With lo
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Table3[Total]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("Table3[Name]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

Upvotes: 1

Views: 1685

Answers (2)

Germany's next Dilbert
Germany's next Dilbert

Reputation: 73

How about adding something like

Worksheets("Leaderboard").Select Worksheets("Leaderboard").Range("A1").Select Worksheets("Data").Select

to select a single cell?

Upvotes: 0

Rory
Rory

Reputation: 34045

It seems to be a quirk of the Table if the sheet isn't active. You can avoid it by using the old Sort method instead:

Sub SortLeaderboard()
    Dim lo                          As Excel.ListObject
    Set lo = Me.ListObjects("Table3")

    With lo
        .Sort.SortFields.Clear
        .Range.Sort key1:=.ListColumns("Total").DataBodyRange.Cells(1), _
                    Order1:=xlDescending, DataOption1:=xlSortNormal, _
                    Key2:=.ListColumns("Name").DataBodyRange.Cells(1), _
                    Order2:=xlAscending, DataOption2:=xlSortNormal, _
                    SortMethod:=xlPinYin, MatchCase:=False
    End With
End Sub

for example.

Upvotes: 1

Related Questions