Find 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
 
L

loren.pottinger

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

loren.pottinger

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

Dave Peterson

Maybe something like:

Option Explicit
Sub testme()

Dim iCtr As Long
Dim myWords As Variant
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim KeepIt As Boolean

myWords = Array("New", "Account")

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
KeepIt = False
For iCtr = LBound(myWords) To UBound(myWords)
If InStr(1, .Cells(iRow, "E").Value, myWords(iCtr), _
vbTextCompare) > 0 Then
KeepIt = True
Exit For
End If
Next iCtr
If KeepIt = True Then
'do nothing
Else
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Test it against a copy--just to be safe.
 
L

loren.pottinger

Hey Dave. Thanks for the reply. I attempted that but it didn't
exactly work. It deleted everything.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.
 
D

Dave Peterson

The code actually looks at the whole cell and if account or new is anywhere in
that cell, that row is kept.

Your data is in column E, right?

And in Sheet1??

If you changed the code, maybe you should post your version.
 
Top