Autofilter tp leave out #N/A in macro [Solved/Closed]

Lavanya - May 27, 2010 at 07:24 AM - Latest reply:  Lavanya
- May 28, 2010 at 05:37 AM
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.
See more 

6 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 27, 2010 at 04:58 PM
11
Thank you
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(...))

Thank you, rizvisa1 11

Something to say? Add comment

CCM has helped 1704 users this month

3
Thank you
thanks a lot for your help.It works now :-)
0
Thank you
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.
0
Thank you
Thanks for the help it worked for me :-)
0
Thank you
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 28, 2010 at 04:18 AM
0
Thank you
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"