Macro to delete rows containing #N/A

[howard]

I have written code to delete all rows to clear data containing #N/A (Columns A to F)

It comes up with a run time error 13 Type Mismatch and the following code is highlighted

If IsError(Cells(i, "A") + 1) Then .Rows(i).Delete

It would be appreciated if you could assist


Sub Delete_NA()
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 4 Step -1

If IsError(Cells(i, "A") + 1) Then .Rows(i).Delete



Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

 

[VoG]

Try

If IsError(Cells(i, "A")) Then .Rows(i).Delete

 

[hiker95]

howard,

'Change this:
'If IsError(Cells(i, "A") + 1) Then .Rows(i).Delete

'To this:
If IsError(.Cells(i, "A")) Then .Rows(i).Delete

 

[howard]

Hi Peter

Thanks for the help, much appreciated

 

[howard]

I have wriiten dcode to delete all rows where the word "showroom" appears in col A, but nothing happens when activating the macro

Sub Del_Showroom()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 6 Step -1
If Range("A" & i).Value Like "*Showroom*" Then Rows(i).Delete
Next i
End Sub

Your assistance will be most appreciated

 

[MrKowz]

Try:

Sub Del_Showroom()
Dim LR As Long, i As Long
LR = Range("A" & rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = LR To 6 Step -1
    If InStr(LCase$(Range("A" & i).Value), "showroom") > 0 Then rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

 

[howard]

Thanks for the help, much appreciated