Find a specific string in a cell

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

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.

loren.pottinger wrote:
> 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

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.

loren.pottinger wrote:
> 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

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.

"loren.pottinger" wrote:
>
> 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

--

Dave Peterson

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.

Dave Peterson wrote:
> 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.
>
> "loren.pottinger" wrote:
> >
> > 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
>
> --
>
> 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.

"loren.pottinger" wrote:
>
> 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.
>
> Dave Peterson wrote:
> > 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.
> >
> > "loren.pottinger" wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

I see you have another thread elsewhere.

I'll drop out.

Dave Peterson wrote:
>
> 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.
>
> "loren.pottinger" wrote:
> >
> > 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.
> >
> > Dave Peterson wrote:
> > > 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.
> > >
> > > "loren.pottinger" wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson

suggest looking in vba help for FIND and then FINDNEXT

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"loren.pottinger" <loren.pottinger@gmail.com> wrote in message
news:1156254731.496431.192850@p79g2000cwp.googlegroups.com...
>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
>

Thanks Don and Dave. I was able to solve it with some help from
elsewhere.
Dave Peterson wrote:
> I see you have another thread elsewhere.
>
> I'll drop out.
>
> Dave Peterson wrote:
> >
> > 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.
> >
> > "loren.pottinger" wrote:
> > >
> > > 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.
> > >
> > > Dave Peterson wrote:
> > > > 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.
> > > >
> > > > "loren.pottinger" wrote:
> > > > >
> > > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson