Fing a specific string in a Cell

[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

 

[Guest]

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

 

[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.

 

[Guest]

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

 

[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.

 

[Guest]

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?)

 

[loren.pottinger]

I'm an excel novice but I got it to work. Thank you so very much!

 

[Guest]

Good news! and thanks for the feedback.

I'm an excel novice but I got it to work. Thank you so very much!