VBA to filterout #N/A If not

[Revathi Kannan]

Hi all,

I have the below macro wherein it will filter the value apart from #N/A and delete the entire row but i need to release the filter if the below doesn't meets the requirement. Please assist

Worksheets("code").Activate
Range("$A$1:$BE$10").AutoFilter Field:=28, Criteria1:="<>#N/A"
With ActiveSheet.AutoFilter.Range
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete

 

[Jeffrey Mahoney]

You should probably add something to your formula that changes an error (#N/A) to something else
=IFERROR(YourFormula,"###")
The reason is #N/A is a little tricky to find unless you create an Array formula

Then add a line in your code to find any ### values

Dim F as Variant
F = Application.Match("###",Range("A1:BE10"))
if IsError(F) = False then
   Worksheets("code").Activate   Range("$A$1:$BE$10").AutoFilter Field:=28, Criteria1:="<>#N/A"
   With ActiveSheet.AutoFilter.Range
     .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
   End With
end if

 

[Revathi Kannan]

Hi Jeffrey,

Thanks for your reply but your code is not working. I have replaced #N/A with blanks. Below is my actual requirement.

>I need to filter the data in column B apart from blanks and delete the entire row.
> If no data found in column B then i need to release the filters and move on to the next subroutine.

Is it possible ?

 

[r1998]

Hello Revathi Kannan
this will go through all the cells in the range and delete the entire row wherever cell = #N/A and shift rows up

Sub a()
Dim r As Range, rr As Range
With Worksheets("code")
Set r = Range("$A$1:$BE$10")
For Each rr In r
If WorksheetFunction.IsNA(rr) Then Rows(rr.Row).EntireRow.Delete shift:=xlUp
Next
End With
End Sub

 

[Revathi Kannan]

Hi r1998,

Thanks for your reply but my request is to run 2 conditions.

Condition 1 - I need to filter the data in column B apart from blanks and delete the entire row.
Condition 2 - If no data found in column B then i need to release the filters and move on to the next subroutine.

 

[r1998]

have not tested this

Sub a()
Dim r As Range, rr As Range
With Worksheets("code")
Set r = Range("$A$1:$BE$10")
If WorksheetFunction.CountA(r) = 0 Then
Call subroutine
Exit Sub
End If
For Each rr In r
If WorksheetFunction.IsNA(rr) Then Rows(rr.Row).EntireRow.Delete shift:=xlUp
Next
End With
End Sub