Fing a specific string in a Cell

L

loren.pottinger

I would like to search all the cells that are not empty in column E for
the words Account and New. If the cell does not have either of those
words in it, I would like to delete that entire row, go to the next
cell in column E and repeat this action until I arrive at an empty
cell.

Please help

LP
 
M

Muhammed Rafeek M

Active AutoFilter (Data-Fileter-AutoFilter) select custom filter from E
column, you will get one dialog box. Select "Equal" and type next column
"Account"
and active OR
Select "Equal" and type next column "New"
then click Ok
you will get all "Account and New"
now you can select all rows and delete
 
L

loren.pottinger

Thank you very much for you reply. I appologize but I forgot to mention
that I am attemting to do this using VBA. Also, I have been attempting
to use the find method, but I have been receiving several errors.
Please help.
 
T

Toppers

Try (but backup data beforehand!):

Sub x()
Dim lastrow As Long, i As Long
With worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "E").End(xlUp).Row
For i = lastrow To 1 Step -1
If Application.And(.Cells(i, "E") <> "", InStr(1, .Cells(i, "E"),
"Account") = 0, InStr(1, .Cells(i, "E"), "New") = 0) Then
Rows(i).EntireRow.Delete
End If
Next i
End with
End Sub
 
L

loren.pottinger

Hey Toppers. Thanks for the reply. I attempted that but it didn't
exactly
work. I believe that I am leaving out some pertinent information. The
cells that I searching do not only have the words Account or New in
them. They are a part of a longer string where those words are just the

beginning of that string. For example: AccountSalary, or NewMachine.
What I want to do is delete the rows of associated with any cell in
column E that do not have the words Account or New as a part of the
value in the cell. Thank you for you help.
 
T

Toppers

It looks for any occurence of Account or New in any part of the string.

If you can post W/book to toppers<at> johntopley.fsnet.co.uk and i'll have
another look. (Maybe be case-sensitive?)
 
Top