在Excel中进行VBA排序会使表内容保持选中状态 [英] VBA Sort in Excel leaves table contents selected

问题描述

我有一个Excel电子表格，该表格基于另一张纸中的数据在一张纸上保存排行榜表.我监视数据表上的更改，然后自动对排行榜表进行排序.一切似乎都正常，但排行榜表的内容在我排序后仍处于选中状态.如何无缝清除选择，而又不影响数据表上的任何选择.

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.

我什至是个好孩子，并使用 With 而不是 Select 命令进行了排序.

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

这就是我所拥有的:

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(代码)-页首横幅表

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

---

推荐答案

如果工作表处于非活动状态，这似乎是Table的一个怪癖.您可以改用旧的Sort方法来避免这种情况:

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

例如.

这篇关于在Excel中进行VBA排序会使表内容保持选中状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！