Autofilter tp leave out #N/A in macro

Solved/Closed
Lavanya - May 27, 2010 at 07:24 AM
 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.

6 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 27, 2010 at 04:58 PM
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(...))
11
thanks a lot for your help.It works now :-)
3
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
Thanks for the help it worked for me :-)
0

Didn't find the answer you are looking for?

Ask a question
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 28, 2010 at 04:18 AM
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"
0