Delete rows macro help

[Jazzer]

Hi Guys,

I am trying to use the following code to delete any row which contains #REF! from B13 to B50

' SORT Macro
'
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 13 Step -1
    If Range("B" & i).Value Like "*#REF!*" Then Rows(i).Delete
Next i
End Sub

However I am getting a run type error 13 - type mismatch

Any ideas where I am going wrong?

Many Thanks
James

 

[villy]

Try this:

Sub del()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 13 Step -1
    If Range("B" & i).Text = "#REF!" Then Rows(i).Delete
Next i
End Sub

 

[Weaver]

You might want to check the formula rather than the value.

Or you could use the iserror() function, but that won't distinguish between ref errors and other errors.