this is untested. i left justified the comments to hopefully keep them from
wrapping

Sub DeleteRows()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
LastRow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1
If .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "#N/A" in Column I, case sensitive.


If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CSVS" in Column I, case sensitive.
ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CMPN" in Column I, case sensitive.
ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CMPN" in Column I, case sensitive.
ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "#N/A" in Column I, case sensitive.
ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete
'This will delete each row with the Value "EXTN" in Column I, case sensitive.
ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete
'This will delete each row with the Value "EXRP" in Column A, case sensitive.
End If
Next

End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub




--


Gary


"Big H" wrote in message
.. .
Hi There,

The code below works almost perfectly, however it does not delete rows which
have #N/A. I believe it has got something to do with the iserror part of the
code. Any help would be appreciated.

tia Harry

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "#N/A" in Column I,
case sensitive.




Sub DeleteRows()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
LastRow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CSVS" in Column I,
case sensitive.

End If
Next
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CMPN" in Column I,
case sensitive.

End If
Next
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete
'This will delete each row with the Value "CMPN" in Column I,
case sensitive.

End If
Next
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "#N/A" in Column I,
case sensitive.

End If
Next
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete
'This will delete each row with the Value "EXTN" in Column I,
case sensitive.

End If
Next
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = LastRow To Firstrow Step -1

If IsError(.Cells(Lrow, "I").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete
'This will delete each row with the Value "EXRP" in Column A,
case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End With
End With
End With
End With
End With
End Sub