Sorting a group of rows together

[gymjohn]

Hi, I'm not entirely sure how to phrase this and can't find the solution through search

Basically, I have a number of teams with 3 people per team. Each team member has a score which ads up to a total score, I would like to sort my teams from highest score to lowest without disrupting the teams

Ciara, 2
Emma, 4
John, 1
Total, 7

James, 3
Steve, 3
Sarah, 2
Total, 8

How do I sort this by total score while keeping team members together?

Thanks

 

[Truiz]

If your data looks like this:

Excel 2012

	A	B
1	Name	Score
2	Ciara	2
3	Emma	4
4	John	1
5	Total	7
6	James	3
7	Steve	3
8	Sarah	2
9	Total	8

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

You can use this to sort it in groups * AS LONG AS ALL TEAMS HAVE 3 MEMBERS*

Sub [COLOR=#574123]gymjohn[/COLOR]()
Dim lRow, i As Long


lRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
For i = 2 To lRow Step 4


    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(i, 2), Cells(i + 2, 2)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Cells(i, 2), Cells(i + 2, 2))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next


End Sub

You should get this result

Excel 2012

	A	B
1	Name	Score
2	Ciara	4
3	Emma	2
4	John	1
5	Total	7
6	James	3
7	Steve	3
8	Sarah	2
9	Total	8

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

 

[Truiz]

I just noticed I made a mistake

If you have this:

Excel 2012

	A	B
1	Name	Score
2	Emma	2
3	Ciara	4
4	John	1
5	Total	7
6	James	3
7	Steve	3
8	Sarah	2
9	Total	8

<tbody>
</tbody>

Hoja1 (3)​

Use this * AS LONG AS ALL TEAMS HAVE 3 MEMBERS*:

Sub Macro1()


Dim lrow, i As Long


lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
For i = 2 To lrow Step 4


    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(i, 2), Cells(i + 2, 2)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Cells(i, 1), Cells(i + 2, 2))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next


End Sub

To get this:

Excel 2012

	A	B
1	Name	Score
2	Ciara	4
3	Emma	2
4	John	1
5	Total	7
6	James	3
7	Steve	3
8	Sarah	2
9	Total	8

<tbody>
</tbody>

Hoja1 (3)​