I want to delete the row if columns C,D and E has

Question

i got an excel with 1000 records, I want to delete the row in my excel if columns C, D and E has #N/A value, i have tried few code from forums but it didn't work. pls help me. here is the example

A | B | C | D | E |(columns)

1 | LA | L | #N/A | #N/A

2 | LA | L | #N/A | L

3 | LA | #N/A| #N/A | L

4 | LA | #N/A| #N/A | #N/A ---- Delete this row

5 | BA | #N/A | #N/A | L

6 | CA | #N/A | #N/A | #N/A -----Delete this row

Answer 1

Give this a try:

Sub KillNAS()
    Dim N As Long, c As String
    c = "#N/A"
    N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = N To 1 Step -1
        If Cells(i, "C").Text = c And Cells(i, "D").Text = c And Cells(i, "E").Text = c Then
            Cells(i, "C").EntireRow.Delete
        End If
    Next
End Sub

Answer 2

Alternate, using a find loop and deleting the found rows all at once at the end:

Sub tgr()

    'Declare variables
    Dim rngFound As Range   'Used for the find loop
    Dim rngDel As Range     'Used to store matching rows
    Dim strFirst As String  'Used to store the first cell address of the find loop to prevent infinite loop

    'Search for #N/A in column C
    Set rngFound = Columns("C").Find("#N/A", Cells(Rows.Count, "C"), xlValues, xlWhole)

    'Make sure something was found
    If Not rngFound Is Nothing Then
        'Found something, record first cell address
        strFirst = rngFound.Address

        'Start loop
        Do
            'Check if cells in column C, D, and E are all #N/A
            If Cells(rngFound.Row, "C").Text = "#N/A" _
            And Cells(rngFound.Row, "D").Text = "#N/A" _
            And Cells(rngFound.Row, "E").Text = "#N/A" Then

                'Found they are all #N/A, store the row in rngDel
                If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)

            End If

            'Advance the loop to the next cell with #N/A in column C
            Set rngFound = Columns("C").Find("#N/A", rngFound, xlValues, xlWhole)

        'Exit loop when back to the first cell
        Loop While rngFound.Address <> strFirst
    End If

    'If rngDel has anything in it, delete all of its rows
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete

    'Object variable cleanup
    Set rngFound = Nothing
    Set rngDel = Nothing

End Sub