Macro to sort data until blank row(s), then repeat

Question

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.

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.

Code from comments:

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

Update from comments:

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.

I've got this code working for the first set of data, but don't know how to loop for the next set of data, ie skip blank cells and start again... 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 — Faminebob, Mar 04 '16 at 13:16

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

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

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

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.

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

edited May 23 '17 at 12:07

Community

1
1

answered Mar 04 '16 at 13:30

That is mega, thank you very much. One further question though - what if I also had players names and player's scores in Columns D and E as well. Could the above code be modified such that it could sort A:B with respect to B (which it already does) and also D:E with respect to E? – Faminebob Mar 04 '16 at 13:58
What (if anything) is in C:C? – Mar 04 '16 at 14:00
Nothing, i've shifted them across so instead of D:E it's C:D with respect to D – Faminebob Mar 04 '16 at 14:01
OK, I'm officially confused. You want to sort A:B first. Then C:D or D:E? – Mar 04 '16 at 14:03
Sorry! Sort A:B with respect to the entries in B (which it already does), and then sort C:D with respect to D in the same manner – Faminebob Mar 04 '16 at 14:04
Brilliant, and finally (i promise) ... If I was to shift this all across such that the data was in F:G instead of A:B and H:I instead of C:D?.... – Faminebob Mar 04 '16 at 14:35
That is going to depend on what your data looks like. The [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx) works on 'islands' of data. That was why I needed to know what was in column C. If you start in column F, you need to know what is in column E or .Columns(1) may not be F:F anymore. It could be E:E or D:D or whatever. You have never shown any sample data and now you are continually exchanging the conditions. This is turning into a [Russian Doll Question](http://meta.stackexchange.com/questions/188625). – Mar 04 '16 at 14:47
Sorry, I know. i haven;t worked out how to share data on here. column E is empty, as is J. No worries if this is one question to much I'll work it out based on what you've taught me already :) thanks a lot – Faminebob Mar 04 '16 at 15:33
If column E:E is blank then it will not become part of the 'island' radiating out from F1 and F:F will be columns(1). just had to change the origin of the currentregion to start at F:F and not A:A. – Mar 04 '16 at 15:35
Works perfect, thanks so much and sorry for changing the goal posts :) – Faminebob Mar 04 '16 at 16:03

Macro to sort data until blank row(s), then repeat

1 Answers1

Linked