Are these macros the best way to make custom sorts easily accessible?

[JenniferMurphy]

I have been working on a sheet to track the rankings of the 32 NFL teams over the current season. The minisheet below is a simplified version of that sheet.

There are three different ways that I want to sort the table:

1. Rank (ascending)
2. Conference (descending), Rank (ascending)
3. Conference (descending), Division (descending), Rank (ascending)

I could not find a simple way to save a custom sort. It seems to me that after I set up a custom sort, Excel ought to offer me the option to save it, but no.

The only options I could find were macros and custom lists. Custom lists looked wierd to me, so I choose macros. This is what I came up with. They seem to work.

Can anyone see any problems or a better way?

'==================================================================
'     Sort #1 - Rank
'==================================================================
Sub SortByRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Rank]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub


'==================================================================
'     Sort #2 - Conference, Rank
'==================================================================
Sub SortByConferenceRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Conference]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Rank]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub


'==================================================================
'     Sort #3 - Conferene, Division, Rank
'==================================================================
Sub SortByConferenceDivisionRank()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("TblNFL[Conference]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Division]"), Order:=xlDescending
        .SortFields.Add Key:=Range("TblNFL[Rank]"), Order:=xlAscending
'        .SetRange Range("TblNFL")  'This caused problems
        .SetRange ActiveSheet.ListObjects("TblNFL").Range
        .Header = xlYes
        .Apply
    End With
End Sub

Sorting.xlsm
	B	C	D	E	F	G	H	I
2	Rank	Team	Conference	Division	Wk 11 Average Rank	Wk 10-11 Average Δ	Wk 11 Record	Wk 10 Average Rank
3	10	San Francisco 49ers	NFC	West	9.8	↑0.2	5-4	10.0
4	13	Arizona Cardinals	NFC	West	12.2	↑3.3	6-4	15.5
5	18	Los Angeles Rams	NFC	West	17.7	↓3.7	4-5	14.0
6	19	Seattle Seahawks	NFC	West	18.8	↓1.8	4-5	17.0
7	14	Altanta Falcons	NFC	South	14.3	↓2.8	6-4	11.5
8	16	Tampa Bay Buccaneers	NFC	South	16.8	↑1.2	4-6	18.0
9	24	New Orleans Saints	NFC	South	25.8	↑2.7	3-7	28.5
10	28	Carolina Panthers	NFC	South	27.3	↑2.7	3-7	30.0
11	1	Detroit Lions	NFC	North	1.6	↓0.6	8-1	1.0
12	7	Minnesota Vikings	NFC	North	7.8	↓0.3	7-2	7.5
13	8	Green Bay Packers	NFC	North	8.1	↓1.6	6-3	6.5
14	21	Chicago Bears	NFC	North	21.0	↓3.5	4-5	17.5
15	5	Philadelphia Eagles	NFC	East	5.6	↑0.9	7-2	6.5
16	9	Washington Commanders	NFC	East	8.7	↓0.7	7-3	8.0
17	25	Dallas Cowboys	NFC	East	25.8	↓3.3	3-6	22.5
18	32	New York Giants	NFC	East	30.2	↓4.2	2-8	26.0
19	2	Kansas City Chiefs	AFC	West	1.8	↑1.2	9-0	3.0
20	11	Los Angeles Chargers	AFC	West	11.6	↑1.4	6-3	13.0
21	17	Denver Broncos	AFC	West	17.5	↑1.5	5-5	19.0
22	31	Las Vegas Raiders	AFC	West	29.5	↑2.0	2-7	31.5
23	12	Houston Texans	AFC	South	11.7	↓2.7	6-4	9.0
24	22	Indianapolis Colts	AFC	South	21.2	↓0.2	4-6	21.0
25	29	Tennessee Titans	AFC	South	28.8	↓0.3	2-7	28.5
26	30	Jacksonville Jaguars	AFC	South	28.8	↓2.3	2-8	26.5
27	4	Baltimore Ravens	AFC	North	3.7	↓0.2	7-3	3.5
28	6	Pittsburgh Steelers	AFC	North	6.9	↑4.1	7-2	11.0
29	15	Cincinnati Bengals	AFC	North	15.6	↑2.4	4-6	18.0
30	27	Cleveland Browns	AFC	North	26.8	↑1.2	2-7	28.0
31	3	Buffalo Bills	AFC	East	3.2	↓0.7	8-2	2.5
32	20	Miami Dolphins	AFC	East	19.6	↑4.4	3-6	24.0
33	23	New York Jets	AFC	East	22.9	↓2.4	3-7	20.5
34	26	New England Patriots	AFC	East	26.7	↑2.3	3-7	29.0
2024 Power rankings

Cell Formulas
Range	Formula
B3:B34	B3	=RANK.EQ([@[Wk 11 Average Rank]],[Wk 11 Average Rank],1)

 

[ExcelToDAX]

If you save it in OneDrive or SharePoint, you can create custom filters and save them by name.

Create and manage Sheet Views in Excel - Microsoft Support

Use Sheet Views in Excel to create custom views that won't interrupt others. For instance, you can sort a table by a certain criteria, and others will still see the full table. Or sort a table in a particular way, but everyone else will see it the way it was originally.

support.microsoft.com

 

[JenniferMurphy]

If you save it in OneDrive or SharePoint, you can create custom filters and save them by name.

Create and manage Sheet Views in Excel - Microsoft Support

Use Sheet Views in Excel to create custom views that won't interrupt others. For instance, you can sort a table by a certain criteria, and others will still see the full table. Or sort a table in a particular way, but everyone else will see it the way it was originally.

support.microsoft.com

Thanks. I'll study that.