Delete Rows with #N/A

[Guest]

Hello,

How can I delele, in a VBA macro, all rows in Columns B & D that contains
#N/A ?
Thanks,

 

[Bernie Deitrick]

Jeff,

If that is the only error that you expect, and you have formulas returning
the errors, then you could use the one liner:

Range("B:B,D").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

Of course, you would need an

On Error Resume Next

before that if it is possible that there aren't any errors.

HTH,
Bernie
MS Excel MVP

 

[Chip Pearson]

Jeff,

Try the following code:


Dim RowNdx As Long
For RowNdx = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If IsError(Cells(RowNdx, "B").Value) = True Then
If Cells(RowNdx, "B").Value = CVErr(xlErrNA) Then
Rows(RowNdx).Delete
End If
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com