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
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
"[email protected]" 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
>
>
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.
Muhammed Rafeek M wrote:
> 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
>
>
> "[email protected]" 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
> >
> >
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
"[email protected]" 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
>
>
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.
Toppers wrote:
> 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
>
> "[email protected]" 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
> >
> >
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" wrote:
> 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.
>
> Toppers wrote:
> > 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
> >
> > "[email protected]" 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'm an excel novice but I got it to work. Thank you so very much!
Toppers wrote:
> 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" wrote:
>
> > 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.
> >
> > Toppers wrote:
> > > 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
> > >
> > > "[email protected]" 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
> > > >
> > > >
> >
> >
Good news! and thanks for the feedback.
"loren.pottinger" wrote:
> I'm an excel novice but I got it to work. Thank you so very much!
>
> Toppers wrote:
> > 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" wrote:
> >
> > > 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.
> > >
> > > Toppers wrote:
> > > > 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
> > > >
> > > > "[email protected]" 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
> > > > >
> > > > >
> > >
> > >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks