Amend code to add a criteria to a 'sort'

[Mel Smith]

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:

    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]

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"

lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8

Why does that have that subtraction at the end?

 

[Mel Smith]

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

 

[Peter_SSs]

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)

 

[Mel Smith]

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

 

[Peter_SSs]

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.

 

[Mel Smith]

Hi Peter,

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

Book1
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3		Ovs	Mdns	Runs	Wkts	Ave	Best	Strike rate	Economy	5 wkt innings	10 wkt match	Wide balls bowled	No balls bowled
4	Player A					0.00	0/0	0.00	0.00	0	0
5	Player B	32.0	2	136		0.00	0/61	0.00	4.25	0	0
6	Player C	409.3	83	1319	31	42.55	4/31	79.26	3.22	0	0	5(17)
7	Player D	241.1	33	899	18	49.94	3/26	80.39	3.73	0	0		11(22)
8	Player E	6.2	3	17	1	17.00	1/17	38.00	2.68	0	0
9	Player F	1.0		1		0.00	0/1	0.00	1.00	0	0
10	Player G	21.0	5	78	1	78.00	1/16	126.00	3.71	0	0
11	Player H	0.1		4		0.00	0/0	0.00	24.00	0	0		2(4)
12	Player I	472.2	141	1256	53	23.70	6/40	53.47	2.66	3	0	2(2)	4(8)
13	Player J	15.0	1	60	1	60.00	1/54	90.00	4.00	0	0
14	Player K	186.5	27	724	11	65.82	4/85	101.91	3.88	0	0	3(8)	3(6)
15	Player L	6.5		35		0.00	0/1	0.00	5.12	0	0
16	Player M	116.5	30	358	22	16.27	9/62	31.86	3.06	2	1		2(5)
17	Player N	180.0	39	559	8	69.88	2/44	135.00	3.11	0	0	2(2)	6(21)
18	Player O					0.00	0/0	0.00	0.00	0	0
19	Player P	7.0		57	1	57.00	1/39	42.00	8.14	0	0		1(2)
20	Player Q	29.1	5	100	2	50.00	1/4	87.50	3.43	0	0
21	Player R	203.3	30	757	20	37.85	4/85	61.05	3.72	0	0	1(5)	3(7)
22	Player S	28.4	1	137	2	68.50	1/64	86.00	4.78	0	0
23	Player T	40.0	14	114	6	19.00	5/82	40.00	2.85	1	0
24	Player U	100.5	18	274	12	22.83	5/90	50.42	2.72	1	0	2(2)	3(6)
25	TOTALS	846.4	432	6885	189	36.43	---	---	---	7	1	15(36)	35(81)
Championship Averages

Cell Formulas
Range	Formula
AG25:AI25,AN25:AO25	AG25	=SUM(AG4:AG24)
AJ25	AJ25	=AH25/AI25

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI4:AI24	Expression	=AI4=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)	text	NO
AJ4:AJ24	Expression	=AJ4=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)	text	NO
AL4:AL24	Expression	=AL4=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)	text	NO
AM4:AM24	Expression	=AM4=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)	text	NO

I then use the following code to make the sort:

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
	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3		Ovs	Mdns	Runs	Wkts	Ave	Best	Strike rate	Economy	5 wkt innings	10 wkt match	Wide balls bowled	No balls bowled
4	Player M	116.5	30	358	22	16.27	9/62	31.86	3.06	2	1		2(5)
5	Player E	6.2	3	17	1	17.00	1/17	38.00	2.68	0	0
6	Player I	472.2	141	1256	53	23.70	6/40	53.47	2.66	3	0	2(2)	4(8)
7	Player C	409.3	83	1319	31	42.55	4/31	79.26	3.22	0	0	5(17)
8	Player D	241.1	33	899	18	49.94	3/26	80.39	3.73	0	0		11(22)
9	Player J	15.0	1	60	1	60.00	1/54	90.00	4.00	0	0
10	Player K	186.5	27	724	11	65.82	4/85	101.91	3.88	0	0	3(8)	3(6)
11	Player N	180.0	39	559	8	69.88	2/44	135.00	3.11	0	0	2(2)	6(21)
12	Player G	21.0	5	78	1	78.00	1/16	126.00	3.71	0	0
13	Player A					0.00	0/0	0.00	0.00	0	0
14	Player B	32.0	2	136		0.00	0/61	0.00	4.25	0	0
15	Player F	1.0		1		0.00	0/1	0.00	1.00	0	0
16	Player H	0.1		4		0.00	0/0	0.00	24.00	0	0		2(4)
17	Player L	6.5		35		0.00	0/1	0.00	5.12	0	0
18	Player O					0.00	0/0	0.00	0.00	0	0
19	Player P	7.0		57	1	57.00	1/39	42.00	8.14	0	0		1(2)
20	Player Q	29.1	5	100	2	50.00	1/4	87.50	3.43	0	0
21	Player R	203.3	30	757	20	37.85	4/85	61.05	3.72	0	0	1(5)	3(7)
22	Player S	28.4	1	137	2	68.50	1/64	86.00	4.78	0	0
23	Player T	40.0	14	114	6	19.00	5/82	40.00	2.85	1	0
24	Player U	100.5	18	274	12	22.83	5/90	50.42	2.72	1	0	2(2)	3(6)
25	TOTALS	846.4	432	6885	189	36.43	---	---	---	7	1	15(36)	35(81)
Championship Averages

Cell Formulas
Range	Formula
AG25:AI25,AN25:AO25	AG25	=SUM(AG4:AG24)
AJ25	AJ25	=AH25/AI25

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI4:AI24	Expression	=AI4=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)	text	NO
AJ4:AJ24	Expression	=AJ4=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)	text	NO
AL4:AL24	Expression	=AL4=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)	text	NO
AM4:AM24	Expression	=AM4=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)	text	NO

 

[Mel Smith]

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

 

[Peter_SSs]

amending the line: lr = Cells(Rows.Count, "AJ").End(xlUp).Row - 8 to -1

That looks much more plausible.

I am now getting the desired results.

Good news! Thanks for letting us know.