Title: Find and delete Rows where cell value is "#N/A"
Tags: vba;excel;find;delete-row
Question: I have an excel document that I use to analyze data sets each data asset I bring in has varying amounts of data.  I have tried to write a macro that I assign to a button that can identify delete rows based on the value of a  cell. It does not work.  What am I doing wrong?

```Sub Button2_Click()
[vb]
'This will find how many rows there are
 With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
 End With

 Sub sbDelete_Rows_Based_On_Criteria()
     Dim lRow As Long
     Dim iCntr As Long
     lRow = lastRow
     For iCntr = lRow To 1 Step -1
         'Replaces XX with the variable you want to delete
         If Cells(iCntr, 1) = "#N/A" Then
             Rows(iCntr).Delete
         End If
     Next
 End Sub
 [/vb]
End Sub
```

Comment: Im sorry I dont understand how you wrote the question; what does your excel spreadsheet looks like? Are you saying that each row is an entry, but each entries can have any amount of columns? could you add a screenshot.

When you press the button what should happen exactly? Delete rows matching a cell?

Comment: When I hit the button I need it to evaluate Colum A & Column B and delete the Rows with #N/A. Only one column has to have N/A in it for the requirement for the delete to be met.  I use the Spreadsheet as a template so the amount of rows I have varies every time I use the SS.

Here is the accepted answer: Your logic is pretty much there, but your syntax is off. Additionally, you are only checking column A for the value and not column B (per your comments above).

```Sub Button2_Click()
    Dim lRow As Long
    'This will find how many rows there are
    With ActiveSheet
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox lastRow
    End With

    Dim iCntr As Long
    For iCntr = lRow To 1 Step -1
        'Replace "#N/A" with the value you want to delete
        ' Check column A and B for the value.
        If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
            Rows(iCntr).Delete
        End If
    Next
End Sub
```

Or simplified:

```Sub Button2_Click()
    Dim iCntr As Long
    For iCntr = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        'Replace "#N/A" with the value you want to delete
        ' Check column A and B for the value.
        If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
            Rows(iCntr).Delete
        End If
    Next
End Sub
```

Comment for this answer: @Dan - I realized after posting you are looking for Excel errors (which are noted as "#NA"). I updated the answer to use the `Text` property instead of `Value`. This should resolve the error.
Comment for this answer: I get an error Run-Time '13" type mismatch on this line    If Cells(iCntr, 1).Value = "#N/A" Or Cells(iCntr, 2).Value = "#N/A" Then

Here is another answer: Because you have two subs, you must pass lastRow from one to the other:

```Sub Button2_Click()
'This will find how many rows there are
 With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
End With

Call sbDelete_Rows_Based_On_Criteria(lastRow)

End Sub

 Sub sbDelete_Rows_Based_On_Criteria(lastRow)
 Dim lRow As Long
 Dim iCntr As Long
 lRow = lastRow
 For iCntr = lRow To 1 Step -1
    'Replaces XX with the variable you want to delete
    If Cells(iCntr, 1).Text = "#N/A" Then
        Rows(iCntr).Delete
    End If
 Next
 End Sub
```

Note:


the sub are unnested
Use .Text



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:01/13/15', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<vba><excel><find><delete-row>', 'Title': 'Find and delete Rows where cell value is "#N/A"', 'ViewCount': 15345, 'id': 27927042, 'user_AboutMe': None, 'user_AccountId': 4906372.0, 'user_DisplayName': 'risail', 'user_Reputation': 460.0, 'user_Views': 110.0, 'user_id': 3953007.0}}