Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fing a specific string in a Cell
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List File Properties - Author | Excel Worksheet Functions | |||
Retrieving a specific cell from multiple worksheets | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Select cell containing specific text &return value from another ce | Excel Worksheet Functions | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) |