宏以对数据进行排序，直到空白行，然后重复

本文介绍了宏以对数据进行排序，直到空白行，然后重复的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在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.