Deleting rows if #N/A or selecting only rows that aren't #N/A

Question

I am building a macro in VBA to do a number of different functions, one of them will be copying data into the report from a different spreadsheet. The master spreadsheet that will be giving the data has data on all clients, I am using a VLOOKUP and then importing all the data that is relevant to that client. Is there a way to either:

1) look down a column for the first item that is #N/A and selecting all data above it (pulling the row number of the first #N/A would be useful and make the second bit easy

2) delete the row is column U is #N/A - the problem I've found with this is that the loop is very big and takes a long time, I used similar to the below link

I want to delete the row if columns C,D and E has #N/A using VBA

[THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) will get you started. — Siddharth Rout, Oct 29 '14 at 13:48

score 1 · Accepted Answer · answered Oct 29 '14 at 14:30

Is this fast enough??:

Sub Macro1()
    Dim N As Long, rFilter As Range
    N = Cells(Rows.Count, "U").End(xlUp).Row
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        Set rFilter = Range("U1:U" & N)
        Set rr = Range("U2:U" & N)
        rFilter.AutoFilter Field:=1, Criteria1:="#N/A"
        Set rkill = rr.Cells.SpecialCells(xlCellTypeVisible)
        rkill.EntireRow.Delete
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    rFilter(1).AutoFilter
End Sub

Deleting rows if #N/A or selecting only rows that aren't #N/A

1 Answers1