Autofilter tp leave out #N/A in macroSolved/Closed

Question

Hello, 

I have a project id in column C , which iam checking from another source data which is there in a differenr tab .So ihave a vlookup fprmulae in the column X to check if the project is present in the other source .For this column X I have applied autofilter .Now what should be the criteria in the macro , for the autofilter to filter only values and leaving out #N/A that are the endresult of the vlookup.Please help me with the code.

rizvisa1 · Accepted Answer

The best and most easy way of this answer would be 
start macro recorder
apply the filter
stop the recorder.

now you would have the code to use


Also you can do what Trowa suggest

=IF(ISERROR(vlookup(...)), "PROJ NOT FOUND", VLOOKUP(...))

Trowa · Answer

Do you really want a macro?
I would use a combination of IF and ISERROR to change #N/A to "" (blank) and then sort the data.

Lavanya · Answer

Thanks for the help it worked for me :-)

Lavanya · Answer

I have recorded the macro to unselect the 0's .BUt the macro got recorded as below .Can you pls advise what is the issue with this pls ? it is taking the project id's ? instead of taking the selection criteria as not equal to 0 from the filter.Please advise

Rows("2:2").Select
    Selection.AutoFilter
    Range("X2").Select
    ActiveSheet.Range("$A$2:$Y$998").AutoFilter Field:=24, Criteria1:=Array( _
        "04716", "04939", "05828", "05936", "06098", "06274", "2202", "2747", "P00213", "P01609" _
        , "P02157", "P02275", "P02335", "P02598", "P04822", "P05005", "P05861"), Operator:= _
        xlFilterValues

rizvisa1 · Answer

If you just wanted to remove #N/A, you should have selected "NOT EQUAL" and choose #N/A

Some thing like this

'remove any filter if present
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

'it seems that your data starts at row 2. (you said Rows("2:2").Select , if not then make it to the row 1)
Rows("2:" & Rows.count).select

'apply filter to sheet
Selection.AutoFilter

' filter on column 24 where value is not equal to #N/A
Selection.AutoFilter Field:=24, Criteria1:="<>#N/A"

if case of 0 it is same
Selection.AutoFilter Field:=24, Criteria1:="<>0"

Lavanya · Answer

thanks a lot for your help.It works now :-)

Autofilter tp leave out #N/A in macro

6 replies

Subscribe To Our Newsletter!