Deleting Rows if a Column has "DELETE" in it

[Rick-Excel]

Hi. Mr. Excel:

I have a small problem. I need a macro to automatically delete a row is a cell in a particular column has the word "DELETE" in it.

For example

Col. A Col. B Col. C Col. D

Row 1 Hi Me You Not

Row 2 Rick Excel Bob DELETE

Row 3 Excel Rick Kim Nothing

Row 4 Day Week Month DELETE

I would like a macro to be able to automatically delete the rows that have the word "DELETE" in column D. Any help would be appreciated. Thank you.

Rick

[AJ]

Hi Rick,

I think this should do the trick.
Let me know of any probs...

Sub deletestuff()

Dim Lastrow As Long
Dim Currentrow As Long


'First, find last cell in column with data
Lastrow = Range("D65536").End(xlUp).Row

'Define start point
Currentrow = 1
Range("D" & Currentrow).Select

Do Until Currentrow = Lastrow + 1

If ActiveCell.Value = "DELETE" Then
Rows(Currentrow & ":" & Currentrow).Select
Selection.Delete Shift:=xlUp
Lastrow = Lastrow - 1
Else
ActiveCell.Offset(1, 0).Select
End If
Currentrow = ActiveCell.Row
Range("D" & Currentrow).Select
Loop

End Sub

[Parra]

Thanks AJ, I just changed some minor things and it worked fine. It except that it was case sensative, but other than that it was fine.

Parra