Creating a dynamic #N/A Filter for EXCEL.

[tysondogerz]

I have a set of data which I need to remove the #N/A for a table. This is the last thing I need to do for my project I am working on. Unfortunately, this has not been as easy as I have imagined. I have already filtered the table and am wanting to additionally add a #N/A filter so the data can look nice and neat.

I have tried:

wb1.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="=#N/A"

All this does it make my data filled with #N/A. Where as I want to remove the #N/A for column 1

The below works though it's not for dynamic data

Sub Macro2()
     '
     ' Macro2 Macro
     '
    
    
     '
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:= _
    Array("Nov 13", "Nov 13", "Today", _
    ), Operator:= _
    xlFilterValues
End Sub
ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="#N/A"

Any ideas. I've been stuck on this for a while now and it's the last thing I need to do so I can finish my project. Thanks.

 

[mole999]

the #N/A is in the table to start with, what is the formula for that column ?

 

[tysondogerz]

Very simple vlookup function in a filtered table.. I have tried it with macro recorder and it gave me:

wb1.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _ Array("*"), Operator:=xlFilterValues

. However the array was for each row so not viable with my dynamic data at all.

sht1.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:= _
        "<>#N/A" merely creates a N/A column.