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:
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
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.
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)
On Error GoTo a
MsgBox "There is nothing to delete", vbInformation, "How about that!"
Hope this helps.
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.