Delete Row if Cell Contents = "ABC"

[Hoopsah]

Hi

I am trying to write a wee macro that will check the contents of a cell and delete a row if certain criteria met.

I have a bit of code:

[VBA]Sub DeleteRow()
' Macro Recorded 17/11/2009 by Gerry McNally
' Check Column E for particular letters - if found then delete row
Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Rng As Range

For i = iLastRow To 1 Step -1
If LCase(Left(Cells(i, "K").Value, 1)) = "CBC" Then
Rows(i).Delete
End If

Next i

Application.ScreenUpdating = True

End Sub[/VBA]


But not only does it not work, but to be honest I was changing things to suit anyway. In the instance above I had created a column (K) that read from Column E the first 3 characters and I tried to creat the macro to check column K and delete a row if it contained "CBC"


But what I would rather do is have a macro thaty would just check column E and if the first 3 letters = either CBC or MBC or SBC then delete the whole row.

Thanks for any help

Hoopsah

[mikerickson]

The posted length is off. Try
[VBA]If LCase(Left(Cells(i, "K").Value, 3)) = "CBC" Then[/VBA]
or you could use Like
[VBA]If LCase(Cells(i,"K").Value) Like "CBC*" Then[/VBA]

[Hoopsah]

Fraid those changes still do not delete any rows

[mikerickson]

Right. We were both comparing LCase to "CBC". UCase would be more appropriate.

[Hoopsah]

Man!!!

Thank you, it is now deleting the rows ok - I can only say in my defence that sometimes I anm quite thick!!

Cheers Dude

Hoopsah

[mdmackillop]

Use Option Compare Text to avoid the need for UCase/LCase operations.

[Hoopsah]

Cheers md

I didn't know about that option but checked it out on ozgrid and it does make ot a lot easier to check

Thanks

Hoops