Menu
Ask a question Report

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

- May 27, 2010 at 07:24 AM - Latest reply:  Lavanya
- May 28, 2010 at 05:37 AM
Excel Ask a question Report
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
Report Respond to rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 - 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 1648 users this month

Report Respond to Lavanya
- May 28, 2010 at 05:37 AM
3
Thank you
thanks a lot for your help.It works now :-)
Report Respond to Trowa
- May 27, 2010 at 09:19 AM
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.
Report Respond to Lavanya
- May 27, 2010 at 11:38 PM
0
Thank you
Thanks for the help it worked for me :-)
Report Respond to Lavanya
- May 28, 2010 at 12:21 AM
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
Report Respond to rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 - 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"
Ask a question