vba Sort with Criteria

[gtd526]

Hello,
Looking to Sort (A:P) by G/L % (L:L) excluding if Column (N:N) is a certain Value (ie "sold").
Thanks.

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
8	Symbol	Sector	Type	Shares	Cost	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %	Weight(%)	Expiration	Expires in:
9	PLTR	#N/A	Call	10	$1.50	$2.37	$50.21	$2.31	$1,500.00	$2,370.00	$870.00	58.00%	2.2%	Dec 15	92	day(s)
10	CRWD	#N/A	Call	10	$19.10	$30.70	$50.21	$19.60	$19,100.00	$30,700.00	$11,600.00	60.73%	28.4%	Dec 15	92	day(s)
11	AXP	#N/A	Call	10	$5.30	$2.06	$50.21	$2.06	$5,300.00	$2,060.00	-$3,240.00	-61.13%	1.9%	Jan 19	127	day(s)
12	PANW	#N/A	Call	10	$25.00	$16.00	$50.21	$8.45	$25,000.00	$16,000.00	-$9,000.00	-36.00%	14.8%	Dec 15	92	day(s)
13	LRCX	#N/A	Call	5	$36.55	$71.47	$71.47	$21.00	$18,275.00	$35,735.00	$17,460.00	95.54%	33.1%	sold
14	ALGN	#N/A	Call	5	$23.00	$37.22	$50.21	$21.00	$11,500.00	$18,610.00	$7,110.00	61.83%	17.2%	sold
15	AMD	#N/A	Call	10	$6.85	$1.43	$50.21	$1.43	$6,850.00	$1,430.00	-$5,420.00	-79.12%	1.3%	sold
16	GOOGL	#N/A	Call	10	$1.76	$1.06	$50.21	$1.06	$1,760.00	$1,060.00	-$700.00	-39.77%	1.0%	sold
17							$0.00	$0.00
18							$0.00	$0.00
19							$0.00	$0.00
20							$0.00	$0.00
21							$0.00	$0.00
ETNA

Cell Formulas
Range	Formula
G9:G21	G9	=IF($F9=0,0,MAX($F9,$G9))
H9:H21	H9	=IF($H9=0,$F9,MEDIAN($H9,$F9,0))
I9:I21	I9	=IF($A9<>"",IF($C9="Long",D9*E9,IF($C9="Call",((D9*E9)*100),IF($C9="Put",((D9*E9)*100),""))),"")
J9:J21	J9	=IF($A9<>"",IF($C9="Long",D9*F9,IF($C9="Call",((D9*F9)*100),IF($C9="Put",((D9*F9)*100),""))),"")
K9:K21	K9	=IF($A9="","",J9-I9)
L9:L21	L9	=IF($A9="","",K9/I9)
M9:M21	M9	=IF($A9="","",J9/$J$22)
C21,C19,C12:C17,C9:C10	C9	=IF(ISTEXT($A9),"Call","")
P21,P19,P9:P17	P9	=IF(OR($N9="",$N9="sold"),"","day(s)")
B9:B21	B9	=IF(COUNTIF(Sectors!$A$2:$ABJ$47,$A9)=1,INDEX(Sectors!$A$2:$ABK$2,MAX((Sectors!$A$2:$ABJ$47=$A9)*(COLUMN(Sectors!$A$2:$ABK$2)))),"")
O9:O21	O9	=IF(OR($N9="",$N9="sold"),"",$N9-TODAY())
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gtd526]

Hello,
Looking to Sort (A:P) by G/L % (L:L) excluding if Column (N:N) is a certain Value (ie "sold").
Thanks.

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
8	Symbol	Sector	Type	Shares	Cost	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %	Weight(%)	Expiration	Expires in:
9	PLTR	#N/A	Call	10	$1.50	$2.37	$50.21	$2.31	$1,500.00	$2,370.00	$870.00	58.00%	2.2%	Dec 15	92	day(s)
10	CRWD	#N/A	Call	10	$19.10	$30.70	$50.21	$19.60	$19,100.00	$30,700.00	$11,600.00	60.73%	28.4%	Dec 15	92	day(s)
11	AXP	#N/A	Call	10	$5.30	$2.06	$50.21	$2.06	$5,300.00	$2,060.00	-$3,240.00	-61.13%	1.9%	Jan 19	127	day(s)
12	PANW	#N/A	Call	10	$25.00	$16.00	$50.21	$8.45	$25,000.00	$16,000.00	-$9,000.00	-36.00%	14.8%	Dec 15	92	day(s)
13	LRCX	#N/A	Call	5	$36.55	$71.47	$71.47	$21.00	$18,275.00	$35,735.00	$17,460.00	95.54%	33.1%	sold
14	ALGN	#N/A	Call	5	$23.00	$37.22	$50.21	$21.00	$11,500.00	$18,610.00	$7,110.00	61.83%	17.2%	sold
15	AMD	#N/A	Call	10	$6.85	$1.43	$50.21	$1.43	$6,850.00	$1,430.00	-$5,420.00	-79.12%	1.3%	sold
16	GOOGL	#N/A	Call	10	$1.76	$1.06	$50.21	$1.06	$1,760.00	$1,060.00	-$700.00	-39.77%	1.0%	sold
17							$0.00	$0.00
18							$0.00	$0.00
19							$0.00	$0.00
20							$0.00	$0.00
21							$0.00	$0.00
ETNA

Cell Formulas
Range	Formula
G9:G21	G9	=IF($F9=0,0,MAX($F9,$G9))
H9:H21	H9	=IF($H9=0,$F9,MEDIAN($H9,$F9,0))
I9:I21	I9	=IF($A9<>"",IF($C9="Long",D9*E9,IF($C9="Call",((D9*E9)*100),IF($C9="Put",((D9*E9)*100),""))),"")
J9:J21	J9	=IF($A9<>"",IF($C9="Long",D9*F9,IF($C9="Call",((D9*F9)*100),IF($C9="Put",((D9*F9)*100),""))),"")
K9:K21	K9	=IF($A9="","",J9-I9)
L9:L21	L9	=IF($A9="","",K9/I9)
M9:M21	M9	=IF($A9="","",J9/$J$22)
C21,C19,C12:C17,C9:C10	C9	=IF(ISTEXT($A9),"Call","")
P21,P19,P9:P17	P9	=IF(OR($N9="",$N9="sold"),"","day(s)")
B9:B21	B9	=IF(COUNTIF(Sectors!$A$2:$ABJ$47,$A9)=1,INDEX(Sectors!$A$2:$ABK$2,MAX((Sectors!$A$2:$ABJ$47=$A9)*(COLUMN(Sectors!$A$2:$ABK$2)))),"")
O9:O21	O9	=IF(OR($N9="",$N9="sold"),"",$N9-TODAY())
Press CTRL+SHIFT+ENTER to enter array formulas.

I'm using the following Macro, which works. But doesn't exclude a certain value.

Sub ETNA_Sort_by_Gain()

    Workbooks("The Whole Enchilada.xlsm").Worksheets("ETNA").Select
    
    Range("A9").Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    
    ActiveWorkbook.Worksheets("ETNA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ETNA").Sort.SortFields.Add2 Key:=Range("L9", Range("L9").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("ETNA").Sort
        .SetRange Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A9").Select
    
End Sub