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

 

[RobP]

Hi do you mean you need the sort to be DEscending, rather than Ascending ? (so biggest to smallest therefore, which puts zeros at the bottom ?

You could just ammend this part of the code I think :

Order:=xlAscending

to

 Order:=xlDescending

 

[Mel Smith]

Hi Rob,

No, I still need the sort to be Ascending but with the added criteria that cells in the sort range that are zero are list last rather than first. something like: 1, 2, 3, 4 etc then 0, 0, 0

Mel

 

[RobP]

I thought too easy to be true..

So the way I have done it is to use a "helper" column in your data, which will contain 1 for numbers greater than zero, and a 0 for the rows containing well.. a zero.

So I used (for my example) Column AL, and inserted a formula to get all the helper data down the column:

=IF(AJ4>0,1,0)

which basically when copied down your data gives you the 1's and 0's needed.

Then, after your initial sort, I just sorted again based on this column AL, but in a Descending manor. There might be some cleaner code out there, but this worked for me anyway by just inserting it after your initial sort.

Of course, you may have data in Col AL - so use whichever column you see fit.

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:= _
        Range("AL4:AL" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("AE3:AQ" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Mel Smith]

Many thanks, Rob,

I'll try that.
Mel

 

[Peter_SSs]

Assuming that the values in column AJ are numerical and not the result of formulas then another option might be to
- include in your code before the sort a Find/Replace to replace 0 with "z"
- do the sort as you already have it
- include in your code after the sort a Find/Replace to replace "z" with 0

 

[Mel Smith]

Hello Peter,

I think your idea will work better for me. How can I amend my code to Find/Replace my zeros to "z" and vice versa?

Mel

 

[Peter_SSs]

I don't have your whole code or sample data to test, but assuming lr correctly identifies the last row to operate on try something like this

Line that evaluates lr

Range("AJ4:AJ" & lr).Replace What:=0, Replacement:="z", LookAt:=xlWhole

The rest of your sorting code, assuming that is working

Range("AJ4:AJ" & lr).Replace What:="z", Replacement:=0, LookAt:=xlWhole

 

[Mel Smith]

Hello Peter,

Thank you for your code, which partially worked. 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'm trying to work out why.

Mel

 

[Mel Smith]

Peter,

Below is the befgore and after.

Mel

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Before:
2		Ovs	Mdns	Runs	Wkts	Ave	Best	Strike rate	Economy	5 wkt innings	10 wkt match	Wide balls bowled	No balls bowled
3						0.00	0/0	0.00	0.00	0	0
4		32.0	2	136		0.00	0/61	0.00	4.25	0	0
5		409.3	83	1319	31	42.55	4/31	79.26	3.22	0	0	5(17)
6		241.1	33	899	18	49.94	3/26	80.39	3.73	0	0		11(22)
7		6.2	3	17	1	17.00	1/17	38.00	2.68	0	0
8		1.0		1		0.00	0/1	0.00	1.00	0	0
9		21.0	5	78	1	78.00	1/16	126.00	3.71	0	0
10		0.1		4		0.00	0/0	0.00	24.00	0	0		2(4)
11		472.2	141	1256	53	23.70	6/40	53.47	2.66	3	0	2(2)	4(8)
12		15.0	1	60	1	60.00	1/54	90.00	4.00	0	0
13		186.5	27	724	11	65.82	4/85	101.91	3.88	0	0	3(8)	3(6)
14		6.5		35		0.00	0/1	0.00	5.12	0	0
15		116.5	30	358	22	16.27	9/62	31.86	3.06	2	1		2(5)
16		180.0	39	559	8	69.88	2/44	135.00	3.11	0	0	2(2)	6(21)
17						0.00	0/0	0.00	0.00	0	0
18		7.0		57	1	57.00	1/39	42.00	8.14	0	0		1(2)
19		29.1	5	100	2	50.00	1/4	87.50	3.43	0	0
20		203.3	30	757	20	37.85	4/85	61.05	3.72	0	0	1(5)	3(7)
21		28.4	1	137	2	68.50	1/64	86.00	4.78	0	0
22		40.0	14	114	6	19.00	5/82	40.00	2.85	1	0
23		100.5	18	274	12	22.83	5/90	50.42	2.72	1	0	2(2)	3(6)
24	TOTALS	846.4	432	6885	189	0.00	---	0.00	0.00	7	1	15(36)	35(81)
25
26	After:
27		Ovs	Mdns	Runs	Wkts	Ave	Best	Strike rate	Economy	5 wkt innings	10 wkt match	Wide balls bowled	No balls bowled
28		116.5	30	358	22	16.27	9/62	31.86	3.06	2	1		2(5)
29		6.2	3	17	1	17.00	1/17	38.00	2.68	0	0
30		472.2	141	1256	53	23.70	6/40	53.47	2.66	3	0	2(2)	4(8)
31		409.3	83	1319	31	42.55	4/31	79.26	3.22	0	0	5(17)
32		241.1	33	899	18	49.94	3/26	80.39	3.73	0	0		11(22)
33		15.0	1	60	1	60.00	1/54	90.00	4.00	0	0
34		186.5	27	724	11	65.82	4/85	101.91	3.88	0	0	3(8)	3(6)
35		180.0	39	559	8	69.88	2/44	135.00	3.11	0	0	2(2)	6(21)
36		21.0	5	78	1	78.00	1/16	126.00	3.71	0	0
37						0.00	0/0	0.00	0.00	0	0
38		32.0	2	136		0.00	0/61	0.00	4.25	0	0
39		1.0		1		0.00	0/1	0.00	1.00	0	0
40		0.1		4		0.00	0/0	0.00	24.00	0	0		2(4)
41		6.5		35		0.00	0/1	0.00	5.12	0	0
42						0.00	0/0	0.00	0.00	0	0
43		7.0		57	1	57.00	1/39	42.00	8.14	0	0		1(2)
44		29.1	5	100	2	50.00	1/4	87.50	3.43	0	0
45		203.3	30	757	20	37.85	4/85	61.05	3.72	0	0	1(5)	3(7)
46		28.4	1	137	2	68.50	1/64	86.00	4.78	0	0
47		40.0	14	114	6	19.00	5/82	40.00	2.85	1	0
48		100.5	18	274	12	22.83	5/90	50.42	2.72	1	0	2(2)	3(6)
49	TOTALS	846.4	432	6885	189	0.00	---	0.00	0.00	7	1	15(36)	35(81)
Sheet1

Cell Formulas
Range	Formula
C24:E24,J49:K49,C49:E49,J24:K24	C24	=SUM(C3:C23)
F24,H49:I49,F49,H24:I24	F24	=MIN(F3:F23)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E28:E48	Expression	=E28=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)	text	NO
F28:F48	Expression	=F28=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)	text	NO
H28:H48	Expression	=H28=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)	text	NO
I28:I48	Expression	=I28=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)	text	NO
E3:E23	Expression	=E3=MAXIFS($AI$4:$AI$24,$AI$4:$AI$24,">"&0)	text	NO
F3:F23	Expression	=F3=MINIFS($AJ$4:$AJ$24,$AJ$4:$AJ$24,">"&0)	text	NO
H3:H23	Expression	=H3=MINIFS($AL$4:$AL$24,$AL$4:$AL$24,">"&0)	text	NO
I3:I23	Expression	=I3=MINIFS($AM$4:$AM$24,$AM$4:$AM$24,">"&0)	text	NO