Solved: Type mismatch error on If Not Range...

[freybe06]

Can someone please help me with this? I've looked all around and I haven't found a reason why this code would not work. I keep getting a Type mismatch error on the "If Not Range..." row.

[vba]
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Not Range("B" & i).Value Like "*Manor*" Then Rows(i).Delete
Next i
[/vba]

This is part of a bigger macro but I can't get past this - the code should go through column B and any row where the cell in column B doesn't have the word "Manor" in it should be deleted.

Thanks for your help! This is really irritating!


Edit: I've added "IsError" in front of Range("B"...) and now my issue is that it is deleting everything. Even if the cell contains the word "Manor", it still deletes it.

[Simon Lloyd]

We'd need to see the rest of the code however try changing Rows(i).Delete for Rows(i).EntireRow.Delete

[Aflatoon]

Perhaps
[vba]Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Not IsError(Range("B" & i).Value) Then
If Range("B" & i).Value Like "*Manor*" Then Rows(i).Delete
end if
Next i [/vba]

[freybe06]

Thanks for the responses guys.

Simon - the macro is pretty large, but this portion is at the beginning and isn't really effected by the rest. However, I will say, column B does include cells that contain "#N/A". Its not a formula because I do a copy/paste values but the #N/A is still there.

Aflatoon - That code deletes all the "Manor" lines. I'm trying to delete everything except the "Manor" lines.


Also, it seems like the code is deleting the header row. Would you guys know how to clear that up as well?

Thanks!

[Aflatoon]

Apologies - missed a Not
[vba]Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Not IsError(Range("B" & i).Value) Then
If Not Range("B" & i).Value Like "*Manor*" Then Rows(i).Delete
End If
Next i [/vba]

[freybe06]

Awesome! That works great! I just have one last little issue. All the lines that have #N/A are still left. Is there a way to work it in to this code to remove those as well? They aren't formulas since I've done a copy/paste special but for some reason the code is still leaving them in.

Thanks again!

[Aflatoon]

[vba]Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Not IsError(Range("B" & i).Value) Then
If Not Range("B" & i).Value Like "*Manor*" Then Rows(i).Delete
Else
Rows(I).delete
End If
Next i [/vba]

[freybe06]

Sir, you are a gentleman and scholar. It works great. Thank you for all your help.

[Aflatoon]

I am neither, but thank you anyway.