CODEWITHSUNDEEP
excelvbams-access
GalacticPonderer
GalacticPonderer

Reputation: 547

Delete row containing #N/A

I am trying to delete rows from a table where a value within that row is #N/A

Sub remove_na()

Dim ws As Worksheet

Dim r As Variant
Dim i As Variant

Set ws = ThisWorkbook.ActiveSheet

r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For Each i In Range("A1:A" & r)
    If i = "#N/A" Then i.EntireRow.Delete
Next

End Sub

I get a Type Mismatch error on the IF statement. I tried using WHILE loops as well as FOR. The same issue occurred.

An example of the data being processed is columns of data (a few hundred records):

#N/A        #N/A
#N/A        #N/A
#N/A        #N/A
#N/A        #N/A
#N/A        #N/A
#N/A        #N/A
#N/A        #N/A
456699.512  6523052.64
456699.512  6523052.64
456699.512  6523052.64
456699.512  6523052.64
456699.512  6523052.64

Upvotes: 1

Views: 1046

Answers (2)

Michal
Michal

Reputation: 5973

Sub remove_na()

Dim ws As Worksheet

Dim r As Integer
Dim i As Integer

Set ws = ThisWorkbook.ActiveSheet

r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = r To 1 Step -1
    If IsError(Cells(i, 1).Value) Then Rows(i).EntireRow.Delete
Next

End Sub

Edited to improve answer

Upvotes: 1

Samuel Everson
Samuel Everson

Reputation: 2102

You can use the IsError() function to evaluate if the cell contains the #N/A error within your If...Then statement.

Like so: 

If IsError(i.Value) Then
    i.EntireRow.Delete
End If

Note: Not tested due to lack of excel on this workstation.

Upvotes: 1

Related Questions