宏以对数据进行排序，直到空白行，然后重复 [英] Macro to sort data until blank row(s), then repeat

问题描述

在A列中，有一个球员姓名列表，在B列中，有他们的得分.玩家数量不一，其后是空白行的未设置数量，其次是其他玩家列表.

In column A I have a list of players names, in column B I have their scores. there are a varying number of players followed by an unset number of blank rows, followed by another list of players.

我需要在vba中使用一个宏，该宏将根据玩家的得分(B列)以降序对A列和B列进行排序，但直到它到达空白行为止.然后，当它到达空白行时，它将跳到下一组播放器并以相同的方式对其进行排序，继续循环直到对所有数据进行排序.

I need a macro in vba that will sort columns A and B in descending order based on the player's score (column B), but only until it hits the blank row(s). Then once it hits the blank row(s) it will jump to the next set of players and sort them in the same way, continuing in a loop until all the data is sorted.

注释代码:

Dim N As Long
N = Cells(1, 1).End(xlDown).Row
Range("A1:B" & N).Sort Key1:=Range("B1:B" & N), Order1:=xlDescending, Header:=xlGuess

评论更新:

应在每个组上执行两个顺序排序. F:G，其中G:G是主键，然后是H:I，其中I:I是主键.

Two sequential sorts should be performed on each group. F:G with G:G being the primary key then H:I with I:I being the primary key.

推荐答案

尝试避免 Range.选择¹方法.通过变量跟踪位置并将其用于直接引用是首选方法.

Try to avoid Range .Select¹ method when referencing the cells on the worksheet. Tracking the position through variables and using these for direct referencing is the preferred method.

Sub playersort()
    Dim i As Long, rw As Long
    rw = 1
    With Worksheets("Players_Scores")
        Do While rw < .Cells(Rows.Count, "A").End(xlUp).Row
            With .Cells(rw, 6).CurrentRegion
                With .Resize(.Rows.Count, 2)
                    .Cells.Sort Key1:=.Columns(2), Order1:=xlDescending, _
                                Key2:=.Columns(1), Order2:=xlAscending, _
                                Orientation:=xlTopToBottom, Header:=xlYes   '<~~ you should know if you have a header or not!
                End With
                With .Resize(.Rows.Count, 2).Offset(0, 2)
                    .Cells.Sort Key1:=.Columns(2), Order1:=xlDescending, _
                                Key2:=.Columns(1), Order2:=xlAscending, _
                                Orientation:=xlTopToBottom, Header:=xlYes   '<~~ you should know if you have a header or not!
                End With
            End With
            For i = 1 To 2
                rw = .Cells(rw, 1).End(xlDown).Row
            Next i
        Loop
    End With
End Sub

通过保持 rw var更新，向下移动两次以跳过空白行是简单的事情.

By keeping the rw var updated, shifting down twice to skip the blank rows is a simple matter.

您真的应该知道您的数据是否具有列标题标签行. xlGuess 可能大部分时间都适用于已记录的代码，但这根本不可靠.

You really should know if your data has a column header label row or not. The xlGuess may work for recorded code most of the time but it simply isn't reliable.

¹_{请参阅如何避免在Excel VBA宏提供了更多摆脱依赖选择和激活来实现目标的方法.}

¹ _{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.}

这篇关于宏以对数据进行排序，直到空白行，然后重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！