Remove rows contains #N/A

[Negi1984]

Hi All,

I am using the below code for removing all the rows contains #N/A. but its giving me error if there is no #N/A in any row.
Could you please suggest in which line I need to modify the code and how ?

Sub Delete_NA()
    Dim N As Long, rFilter As Range
    Sheets("BDR_Table Utran_HWF_RETSUBUNIT").Select
    Range("V3").Select
    N = Cells(Rows.Count, "V").End(xlUp).Row
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        Set rFilter = Range("V2:V" & N)
        Set rr = Range("V3:V" & N)
        rFilter.AutoFilter Field:=22, Criteria1:="#N/A"
        Set rkill = rr.Cells.SpecialCells(xlCellTypeVisible)
        rkill.EntireRow.Delete
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    rFilter(1).AutoFilter
End Sub

Thanks for your valuable feedback.

Regards,
Rajender

 

[Peter_SSs]

Try these alterations

        rFilter.AutoFilter Field:=1, Criteria1:="#N/A"
        On Error Resume Next
        Set rkill = rr.Cells.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not IsEmpty(rkill) Then rkill.EntireRow.Delete

 

[Negi1984]

Hi Peter,

Thanks a lot for your prompt support.
just want to know what is the difference of using Field :-1 while I was using Field :-22(come when I record macro).

Thanks for clearing my doubt.

Regards,
Rajender

 

[Peter_SSs]

just want to know what is the difference of using Field :-1 while I was using Field :-22(come when I record macro).

When you recorded your macro, it appears that you would have put the AutoFilter on many columns, starting from Column A and then filtered on column V. In that case, column V that you have filtered is column 22 of that range.

However, in your code you have Set rFilter = Range("V2:V" & N) so rFilter is a single-column range. You then have rFilter.AutoFilter Field:=22, Criteria1:="#N/A"
That is, you are trying to filter on column 22 of a single-column range - that clearly doesn't work.

 

[Negi1984]

Thanks a lot Peter for clearing my doubt and providing the quick solution.

Have a nice day to you.

Regards,
Rajender

 

[Peter_SSs]

Thanks a lot Peter for clearing my doubt and providing the quick solution.

Have a nice day to you.

Regards,
Rajender

No problem. Glad to help.