PDA

View Full Version : Delete Row if Cell Contents = "ABC"



Hoopsah
11-17-2009, 07:48 AM
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:

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


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
11-17-2009, 07:50 AM
The posted length is off. Try
If LCase(Left(Cells(i, "K").Value, 3)) = "CBC" Then
or you could use Like
If LCase(Cells(i,"K").Value) Like "CBC*" Then

Hoopsah
11-17-2009, 07:56 AM
Fraid those changes still do not delete any rows

:dunno

mikerickson
11-17-2009, 07:58 AM
Right. We were both comparing LCase to "CBC". UCase would be more appropriate.

Hoopsah
11-17-2009, 08:11 AM
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
11-17-2009, 09:33 AM
Use Option Compare Text to avoid the need for UCase/LCase operations.

Hoopsah
11-18-2009, 01:26 AM
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