Adding a second criteria to VBA

M

Marvo

Board Regular
Joined
Nov 27, 2023
Messages
195
Office Version
  1. 2021
Platform
  1. Windows
Hi, how do I add a second criteria to the following VBA please. The second criteria in the table (Buckinghamshire) as a header ID. Many thanks.

Sub Sortintable()

Dim ws As Worksheet
Set ws = ActiveSheet

With ws.ListObjects("Buckinghamshire").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("Buckinghamshire[TOTAL]"), Order:=xlDescending
.Apply
End With
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Just to show you what I'm trying to achieve, in the table, PYM should be below PEARCE as his number is higher in column ID but its not happening. (The ID column is used to sort alphabetically.

Midshires Website Pages.xlsm
ABCDEFGHIJKLMNOPQR
17TOTALPLAYER123456789101112131415ID
1814John MARVESLEY1273716830232925161486146
1914Alan MITCHELL19923113161615881424532160
2014Steve SMITH7231131252018172271511911208
2113Tony MUNTING139129412111345161818166
2212Tony GROUT306412322249926787
2312Keith PYM1512220191182217202029188
2412Steve PEARCE222521222727912212124181
2511Darren KOLEK21202728211713192410123
BEDS
Cell Formulas
RangeFormula
A18:A25A18=COUNTA(C18:Q18)
R18:R25R18=XLOOKUP([@PLAYER],'Players List'!$B$1:$B$244,'Players List'!$A$1:$A$244,,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Brian ADAMS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Linda ADAMS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Jeff BATTERBEE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Nick BAXTER"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Chris BENNETT"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Richard BROMLEY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Kellie CURT"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Sean CURT"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Rob DAVEY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Paul DERBYSHIRE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Rob DONNELLY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Nic DOWNER"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Bob FORDE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Darryl FORNES"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Rick GARVEY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Dan GELLMAN"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Peter GIBOIN"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Tony GROUT"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="David HAMMOND"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Mark HARVEY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Brian HIMSWORTH"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Chris HODGES"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Shane JENKINS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Tony JUN"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Andrew KEEN"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Steve KIDD"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Mike KNOTT"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Darren KOLEK"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="James KOLEK"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Kevin LEECH"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Steve LEECH"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="John LEONARD"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Glenn LEWIS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Scott LOOM"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Trevor MANKTELOW"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="John MARVESLEY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Dan MCCARTHY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Andy MCGILLIVRAY"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Frank MCKENZIE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Alan MITCHELL"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Daniel MOSS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Tony MUNTING"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Steve PEARCE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Stewart PEARCE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Keith PYM"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Richard SMITH"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Steve SMITH"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="John TAYLOR"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Gary THORPE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Peter VALENTINE"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Martin VANN"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Kevin WHARTON"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Stuart WILLS"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Bob Allwood"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Joe Dooley"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Mick Flack"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Jacques Goodall"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Tony Macken"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Ron Pitt"textNO
B107:U300,R1:U16,J1:J16,P1:P16,A1:B16,D1:D16,A17:U106Cell Value="Gene Rondeau"textNO
 
Upvote 0
Try:

VBA Code: 
Sub Sortintable()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  
  With ws.ListObjects("Buckinghamshire").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("Buckinghamshire[TOTAL]"), Order:=xlDescending
    .SortFields.Add Key:=Range("Buckinghamshire[ID]"), Order:=xlDescending
    .Apply
  End With
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
273
RoryA
RoryA
M
  • Solved
How To Modify Sorting Code to Run When Table Name Is Not Known
Replies
3
Views
281
Akuini
Akuini
T
  • Solved
Can I sort a table by applying columns in a different order to the sort request?
Replies
5
Views
204
TheRedCardinal
T
F
  • Solved
Sorting by two columns across all tabs- VBA
2
Replies
14
Views
577
frustrated_macro
F
D
  • Solved
For all named tables change name
Replies
4
Views
75
darryl47nopra
D

Forum statistics

Threads
1,224,862
Messages
6,181,465
Members
453,045
Latest member
Abraxas_X

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top