Amend code to add a criteria to a 'sort'

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
967
Office Version
  1. 365
Platform
  1. Windows
I am using the following code, which works well, but now I find that I need to add a criteria to the sort. The code sorts column AJ smallest to largest but now I would like to perform the same sort but cells in the range that are zero need to be placed last, rather than after being sorted ending up at the top of the list.

My code is:
VBA Code:
    lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:= _
        Range("AJ4:AJ" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("AE3:AQ" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Many thanks.
Mel
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,487
Office Version
  1. 365
Platform
  1. Windows
For some reason it didn't move all the zero records to the bottom but rather grouped them together in the middle of the table, which seems rather odd.
I suspect that has something to do with the calculation of lr which is why I qualified my response with "assuming lr correctly identifies the last row to operate on"
Rich (BB code):
lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8
Why does that have that subtraction at the end?
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
967
Office Version
  1. 365
Platform
  1. Windows
Because outside of the range I want to sort there are several rows that I don't want to include. The number of rows will vary in the "sort range" hence the -8

Mel
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,487
Office Version
  1. 365
Platform
  1. Windows
Because outside of the range I want to sort there are several rows that I don't want to include. The number of rows will vary in the "sort range" hence the -8

Mel
That doesn't appear to be the case with your before/after sample above. If we remove the 'After' and change column AJ for column F (or H or I or .. we are just guessing as you code does not match the data) then lr will evaluate to 16 so your whole code is set to operate only to row 16, whereas I assume you want to 23 (or perhaps 24)
 
Upvote 0

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
967
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

I have several macros that I use before the one we are reviewing. The original table starts with 60 rows of potential data, I then remove rows that do not have relevant data on a match-by-match basis which means that after one match it will only be 11 rows but as the season goes one this number will increase, which is why the macro range needs to be variable in length. Below the last range of data are then several "total" rows.

Mel
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,487
Office Version
  1. 365
Platform
  1. Windows
If you need further help then please provide a dummy but representative small set of sample data in the correct range, the full actual sorting code that you are using with that sample data and explain your requirement in relation to that sample data and code.
 
Upvote 0

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
967
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

I shall do my best. The first set is the unsorted data, after I have removed rows that are unused:

Book1
AEAFAGAHAIAJAKALAMANAOAPAQ
3OvsMdnsRunsWktsAveBestStrike rateEconomy5 wkt innings10 wkt matchWide balls bowledNo balls bowled
4Player A0.000/00.000.0000
5Player B32.021360.000/610.004.2500
6Player C409.38313193142.554/3179.263.22005(17)
7Player D241.1338991849.943/2680.393.730011(22)
8Player E6.2317117.001/1738.002.6800
9Player F1.010.000/10.001.0000
10Player G21.0578178.001/16126.003.7100
11Player H0.140.000/00.0024.00002(4)
12Player I472.214112565323.706/4053.472.66302(2)4(8)
13Player J15.0160160.001/5490.004.0000
14Player K186.5277241165.824/85101.913.88003(8)3(6)
15Player L6.5350.000/10.005.1200
16Player M116.5303582216.279/6231.863.06212(5)
17Player N180.039559869.882/44135.003.11002(2)6(21)
18Player O0.000/00.000.0000
19Player P7.057157.001/3942.008.14001(2)
20Player Q29.15100250.001/487.503.4300
21Player R203.3307572037.854/8561.053.72001(5)3(7)
22Player S28.41137268.501/6486.004.7800
23Player T40.014114619.005/8240.002.8510
24Player U100.5182741222.835/9050.422.72102(2)3(6)
25TOTALS846.4432688518936.43---------7115(36)35(81)
Championship Averages
Cell Formulas
RangeFormula
AG25:AI25,AN25:AO25AG25=SUM(AG4:AG24)
AJ25AJ25=AH25/AI25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AI4:AI24Expression=AI4=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)textNO
AJ4:AJ24Expression=AJ4=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)textNO
AL4:AL24Expression=AL4=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)textNO
AM4:AM24Expression=AM4=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)textNO


I then use the following code to make the sort:
VBA Code:
Dim lr As Long
   
'   Find last row in column AJwith data

    lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8
   
   Range("AJ4:AJ" & lr).Replace What:=0, Replacement:="z", LookAt:=xlWhole
   
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:= _
        Range("AJ4:AJ" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("AE3:AQ" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("AJ4:AJ" & lr).Replace What:="z", Replacement:=0, LookAt:=xlWhole
    
End Sub

And this is the result:

Book1
AEAFAGAHAIAJAKALAMANAOAPAQ
3OvsMdnsRunsWktsAveBestStrike rateEconomy5 wkt innings10 wkt matchWide balls bowledNo balls bowled
4Player M116.5303582216.279/6231.863.06212(5)
5Player E6.2317117.001/1738.002.6800
6Player I472.214112565323.706/4053.472.66302(2)4(8)
7Player C409.38313193142.554/3179.263.22005(17)
8Player D241.1338991849.943/2680.393.730011(22)
9Player J15.0160160.001/5490.004.0000
10Player K186.5277241165.824/85101.913.88003(8)3(6)
11Player N180.039559869.882/44135.003.11002(2)6(21)
12Player G21.0578178.001/16126.003.7100
13Player A0.000/00.000.0000
14Player B32.021360.000/610.004.2500
15Player F1.010.000/10.001.0000
16Player H0.140.000/00.0024.00002(4)
17Player L6.5350.000/10.005.1200
18Player O0.000/00.000.0000
19Player P7.057157.001/3942.008.14001(2)
20Player Q29.15100250.001/487.503.4300
21Player R203.3307572037.854/8561.053.72001(5)3(7)
22Player S28.41137268.501/6486.004.7800
23Player T40.014114619.005/8240.002.8510
24Player U100.5182741222.835/9050.422.72102(2)3(6)
25TOTALS846.4432688518936.43---------7115(36)35(81)
Championship Averages
Cell Formulas
RangeFormula
AG25:AI25,AN25:AO25AG25=SUM(AG4:AG24)
AJ25AJ25=AH25/AI25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AI4:AI24Expression=AI4=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)textNO
AJ4:AJ24Expression=AJ4=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)textNO
AL4:AL24Expression=AL4=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)textNO
AM4:AM24Expression=AM4=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)textNO
 
Upvote 0

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
967
Office Version
  1. 365
Platform
  1. Windows
Peter,

Thank you for your assistance and pertinent suggestions. I have slightly re-arranged the order of operation of the previous macros in use and after incorporation your suggested code and amending the line: lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8 to -1 I am now getting the desired results.

Many thanks.

Mel
 
Upvote 0

Forum statistics

Threads
1,186,414
Messages
5,957,716
Members
438,317
Latest member
Unkle6ens

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
Top