Autofilter tp leave out #N/A in macro
[Solved/Closed]
Related:
- Vba autofilter not equal to
- Autofilter not equal to vba - Best answers
- Excel vba autofilter not equal to - Best answers
- Macro to copy data from one sheet to another based on criteria ✓ - Forum - Excel
- Compare two excel sheets and highlight differences macro ✓ - Forum - Excel
- Run macro automatically when cell value changes ✓ - Forum - Excel
- Can you leave snapchat while on a call - How-To - Mobile
- Macro to insert row in excel based on criteria ✓ - Forum - Excel
6 replies
rizvisa1
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
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(...))
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
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.
I would use a combination of IF and ISERROR to change #N/A to "" (blank) and then sort the data.
Lavanya
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
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
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
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"
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"
