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

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

Would you mind posting the best code you have so far? Then we can insert, what I think, will be a single line to test. — Bathsheba, Aug 21 '13 at 13:34
possible duplicate of [delete row based on condition](http://stackoverflow.com/questions/11317172/delete-row-based-on-condition) — d-stroyer, Aug 21 '13 at 13:35

score 1 · Answer 1 · answered Aug 21 '13 at 14:42

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

answered Aug 21 '13 at 14:42

Gary's Student

95,722
10
59
99

Thanks a lot, it worked great, i am beginner, code was helpful and easy. – user2703679 Aug 22 '13 at 12:06

tigeravatar · Answer 2 · 2013-08-22T15:22:38.843

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

Thanks it worked, just if you could direct me to any source where i could know what the function does? — user2703679, Aug 22 '13 at 12:10
I have edited my answer to provide comments in the code. Hopefully that should help explain what it is doing step-by-step. — tigeravatar, Aug 22 '13 at 15:23

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

2 Answers2

Linked