MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Column Filter

Posted by Lenny on January 22, 2002 12:32 AM

I have a few thousand equipment records that I need to search through.

Posted by Tom Urtis on January 22, 2002 1:15 AM

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
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
Set RtoDel = Application.Union(RtoDel, cell)
End If
End If
On Error GoTo a
Exit Sub
MsgBox "There is nothing to delete", vbInformation, "How about that!"
End Sub

Hope this helps.

Tom Urtis

Posted by Art Farrell on January 23, 2002 7:14 AM


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.

Art Farrell