Creating a dynamic #N/A Filter for EXCEL.

[Tmandoge]

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.


NA homie.jpg






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.

[snb]

You'd beter prevent #N/A from popping up.

[Tmandoge]

In this case, I want to filter out N/A. Something like:

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

though dynamic would be great. As its the only way to clean my data up. It has been some time since I have looked at this VBA code and it shows. I suppose I could alternatively transfer this to another excel, remove N/A...... plus rows that were removed/adjacent to/by N/A. Might be only option at this point.

[SamT]

Column AA has a formula.
Edit the Formula with
IF ISERROR(Original formula), "", (Original Formula))

[Paul_Hossler]

Maybe

Range("AA").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete