Column Filter

OK, here are two macros because I wasn't sure from your post if a cell in column E will just have the word "DELETED" in it, or if someone in your office might input (as you seem to infer) "DELETED--LINE 4 Filling Machine".

If there is only the word "DELETED" in column E, this macro will find that value (in upper case letters) and delete the row:

Sub DeleteTheDELETED()
Dim LastRow As Integer
Dim X As Variant
LastRow = Range("E65536").End(xlUp).Row
For X = LastRow To 1 Step -1
If Range("E" & X).Value = "DELETED" Then
Rows(X).Delete shift:=xlUp
Else
End If
Next X
End Sub

Now, for flexibility, you may have cells in column E that begin with DELETED, such as "DELETED--LINE 4 Filling Machine", or worse yet, only say DELETE (notice the word is altered from DELETED to DELETE, no "D" at the end). This macro should hunt down such renegade entries...remember it goes for the upper case, any cell beginning with DELETE in column E. If no DELETE cells are found, there's a message box to tell you so.

Sub DeleteDeletes()
Dim theCol As Range, cell As Range, RtoDel As Range
Dim LtoDel As String
Set theCol = Range(Range("E1"), Range("E65536").End(xlUp))
LtoDel = "DELETE"
For Each cell In theCol
If Left(cell, 6) = LtoDel Then
If RtoDel Is Nothing Then
Set RtoDel = cell
Else
Set RtoDel = Application.Union(RtoDel, cell)
End If
End If
Next
On Error GoTo a
RtoDel.EntireRow.Delete
Exit Sub
a:
MsgBox "There is nothing to delete", vbInformation, "How about that!"
End Sub

Hope this helps.

Tom Urtis

Hi,

You could set this up in a macro or just do it manually:
1- Select a cell in your list and click on DATA > FILTER >AUTOFILTER.
2- Click on the dropdown arrow on Col. E and select (Custom...).
3- In the left box select 'contains' and in the right box type
'deleted' . You could put in 'delete' or 'delet' and it should work as well.
4- Click OK and you will have a list of the rows containing all
the cells with DELET… in Col. E.
5- Select all the rows below the header and click on Edit > Delete.

CHORDially,
Art Farrell