PDA

View Full Version : Creating a dynamic #N/A Filter for EXCEL.



Tmandoge
11-11-2017, 03:51 AM
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.


20923






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
11-11-2017, 07:20 AM
You'd beter prevent #N/A from popping up.

Tmandoge
11-11-2017, 07:32 AM
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
11-11-2017, 08:29 AM
Column AA has a formula.
Edit the Formula with
IF ISERROR(Original formula), "", (Original Formula))

Paul_Hossler
11-11-2017, 08:36 AM
Maybe




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