Formula stopped working.

[corbie82]

So this worked fine a few months which was the last time i used it but every time i run it now it says

Run-time error 13
Type Mismatch




Sub DeleteRows()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "G").Value = "0" Then
Rows(i).delete
End If
Next i
End Sub

It highlights an issue with this row
If Cells(i, "G").Value = "0" Then

Any ideas ?

 

[ParamRay]

Got any error values in column G, such as #N/A or #VALUE! ?

 

[cooper645]

I concur with ParamRay, an error of that type causes your code to fail.

You can se this to delete those rows as well;

Sub DeleteRows()Dim iLastRow As Long
Dim i As Long
On Error Resume Next
iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "G").Value = "0" Then
Rows(i).Delete
End If
Next i
End Sub

OR if you wish to identify the error and have the option to investigate it then;

Sub DeleteRows()Dim iLastRow As Long
Dim i As Long
On Error GoTo ErrHandle
iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "G").Value = "0" Then
Rows(i).Delete
End If
Next i
Exit Sub
ErrHandle:
If MsgBox("Your error lies in cell " & Cells(i, "G").Address & vbNewLine & "Goto error cell?", vbYesNo + vbCritical, "Error!") = vbYes Then
Cells(i, "G").Select
Else
'Do Nothing & Exit Sub
End If
End Sub