Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
-
 Lavanya -
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

Answer 1 / 6
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 762
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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 6

thanks a lot for your help.It works now :-)
Answer 3 / 6

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.
Answer 4 / 6

Thanks for the help it worked for me :-)
Answer 5 / 6

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
Answer 6 / 6
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 762
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!