Filter N/A and Delete Rows

[vikas_newports]

Hello
I want to Delete the rows have #N/A after apply the ilter and criteria1 #N/A but it delete the one record without #N/A
here is my code

Set rng4 = Range("B5:N" & last_row4)
        With rng4
                .AutoFilter
            .AutoFilter Field:=13, Criteria1:="#N/A"
            On Error Resume Next
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
            .AutoFilter
        End With

 

[vikas_newports]

any luck

 

[vikas_newports]

Any expert will review my code ?

 

[mohadin]

May be other method will do

Dim delrow As Range
    For i = 1 To last_row4
        If Range("N" & i).Text = "#N/A" Then
            If Not delrow Is Nothing Then
                Set delrow = Union(delrow, Range("N" & i))
            Else
                Set delrow = Range("N" & i)
            End If: End If
    Next
    delrow.EntireRow.Delete

 

[vikas_newports]

giving error on this line

delrow.EntireRow.Delete

May be other method will do

Dim delrow As Range
    For i = 1 To last_row4
        If Range("N" & i).Text = "#N/A" Then
            If Not delrow Is Nothing Then
                Set delrow = Union(delrow, Range("N" & i))
            Else
                Set delrow = Range("N" & i)
            End If: End If
    Next
    delrow.EntireRow.Delete

 

[mohadin]

Trap an error if nothing to be deleted

Dim delrow As Range
    For i = 1 To last_row4
        If Range("N" & i).Text = "#N/A" Then
            If Not delrow Is Nothing Then
                Set delrow = Union(delrow, Range("N" & i))
            Else
                Set delrow = Range("N" & i)
            End If: End If
    Next
    If delrow Is Nothing Then: MsgBox ("There Is Nothing To Delete"): Exit Sub
    delrow.EntireRow.Delete

 

[vcoolio]

Hello Vikas,

Try using the AutoFilter as follows:-

Sub Test()

        With Sheet1.Range("N5", Sheet1.Range("N" & Sheet1.Rows.Count).End(xlUp))
                .AutoFilter 1, "#N/A"
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

End Sub

I'm assuming that Row5 is your headings row.
Sheet1 is a sheet code and you may have to change it to suit.

I hope that this helps.

Cheerio,
vcoolio.

 

[vikas_newports]

Thanks Mohadin and vcoolio
both codes are working

 

[vcoolio]

You're welcome Vikas. I'm glad that we were able to help.

Cheerio,
vcoolio.