Use auto filter for remove blank or #value! cell

[YyY_PLM]

Hi ,
I want to use auto filter in VBA, i recoding macro and it filtered by specific values
what I need is to filter all value except #value! and blank cell

Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=LEFT(MID(RC[-13],FIND("" Y :"", RC[-13])+4,LEN(RC[-13])),FIND(""("",MID(RC[-13],FIND("" Y :"",RC[-13])+4,LEN(RC[-13])))-1)"
    Range("N2").Select
       Selection.AutoFill Destination:=Range("N2:N36929"), Type:=xlFillDefault
    Range("N2:N36929").Select
        Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=LEFT(MID(RC[-14],FIND(""Y laser :"",RC[-14])+9,LEN(RC[-14])),FIND("","",MID(RC[-14],FIND(""Y laser :"",RC[-14])+9,LEN(RC[-14])))-1)"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O36929")
    Range("O2:O36929").Select
        Columns("N:N").Select
    Selection.AutoFilter
    ActiveSheet.Range("$N$1:$N$100162").AutoFilter Field:=1, Criteria1:=Array( _
        "1000.2279", "1000.2563", "1000.259", "1000.2591", "1000.2592", "1000.26", _
        "1000.2619", "1000.263", "1000.2635", "1000.2714", "399999.7638", "399999.7656", _
        "399999.7657", "399999.7664", "399999.7665", "399999.7668", "399999.768", _
        "399999.7689", "399999.7696"), Operator:=xlFilterValues
    Range("P100164").Select

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this line

ActiveSheet.Range("$N$1:$N$100162").AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>#VALUE!"

Or possibly better would be to eliminate the #VALUE! errors by modifying the column N formula and then you would only have to filter off the blanks.

"=IFERROR(LEFT(MID(RC[-13],FIND("" Y :"", RC[-13])+4,LEN(RC[-13])),FIND(""("",MID(RC[-13],FIND("" Y :"",RC[-13])+4,LEN(RC[-13])))-1),"""")"

 

[YyY_PLM]

Welcome to the MrExcel board!

Try this line

ActiveSheet.Range("$N$1:$N$100162").AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>#VALUE!"

Or possibly better would be to eliminate the #VALUE! errors by modifying the column N formula and then you would only have to filter off the blanks.

"=IFERROR(LEFT(MID(RC[-13],FIND("" Y :"", RC[-13])+4,LEN(RC[-13])),FIND(""("",MID(RC[-13],FIND("" Y :"",RC[-13])+4,LEN(RC[-13])))-1),"""")"

thx work fine!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.