Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
I need a program which will remove from a largs WorkSheet all rows if the
word "High' is not present in the row. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
You need to be more specific, would high be an entry in any cell in row or just say column A. or the first 5 columns. The longer the range the longer the macro will take to run. Also would it be in a text string or the entire cell eg how high can you jump or just "high" and if it was the later was for example "highly regarded" would it b retained as well? Regards Da -- Da ----------------------------------------------------------------------- Dav's Profile: http://www.excelforum.com/member.php...fo&userid=2710 View this thread: http://www.excelforum.com/showthread.php?threadid=56097 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
Thanks Dav for your interest.
The word "high" will be in column A. It will be a discrete word, but may have other words in the same cell Regards "Dav" wrote: You need to be more specific, would high be an entry in any cell in a row or just say column A. or the first 5 columns. The longer the ranges the longer the macro will take to run. Also would it be in a text string or the entire cell eg how high can you jump or just "high" and if it was the later was for example "highly regarded" would it be retained as well? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560979 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
You could use data|Filter to show the cells that contain "high".
Then delete those visible rows. Record a macro when you do it manually if you want the code. Kanga 85 wrote: Thanks Dav for your interest. The word "high" will be in column A. It will be a discrete word, but may have other words in the same cell Regards "Dav" wrote: You need to be more specific, would high be an entry in any cell in a row or just say column A. or the first 5 columns. The longer the ranges the longer the macro will take to run. Also would it be in a text string or the entire cell eg how high can you jump or just "high" and if it was the later was for example "highly regarded" would it be retained as well? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560979 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
To use Dave's Idea the following should work, much simpler than what was thinking of as I do not really use filter, perhaps I should loo into them a bit more! To use filters, the top row of your sheet has t be a heading row, so the data starts in row 2 Sub Macro4() ' ' Macro4 Macro ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<*high*" Operator:=xlAnd Range("A2:a65533").Select Selection.EntireRow.Delete Selection.AutoFilter Range("A1").Select End Sub Regards Da -- Da ----------------------------------------------------------------------- Dav's Profile: http://www.excelforum.com/member.php...fo&userid=2710 View this thread: http://www.excelforum.com/showthread.php?threadid=56097 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
Thanks Dav and Dave
"Dav" wrote: To use Dave's Idea the following should work, much simpler than what I was thinking of as I do not really use filter, perhaps I should look into them a bit more! To use filters, the top row of your sheet has to be a heading row, so the data starts in row 2 Sub Macro4() ' ' Macro4 Macro ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<*high*", Operator:=xlAnd Range("A2:a65533").Select Selection.EntireRow.Delete Selection.AutoFilter Range("A1").Select End Sub Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560979 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
Dav,
When I used your Sub Macro4() below, it remove all my data below row 1; which is definately not what I wanted. I modified your macro to: Range("A2:A65533").Select Selection.AutoFilter Field:=1, Criteria1:="<*high*", Operator:=xlAnd Selection.EntireRow.Delete Range("A1").Select which works well for all data below row 2, but which always deletes row 2 whether it meets the criteria or not. This means that if I subsequently run the same macro, I then just loose row 2 each time. Any suggestions? Thanks, "Dav" wrote: To use Dave's Idea the following should work, much simpler than what I was thinking of as I do not really use filter, perhaps I should look into them a bit more! To use filters, the top row of your sheet has to be a heading row, so the data starts in row 2 Sub Macro4() ' ' Macro4 Macro ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<*high*", Operator:=xlAnd Range("A2:a65533").Select Selection.EntireRow.Delete Selection.AutoFilter Range("A1").Select End Sub Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560979 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove all rows which do Not contain a particular word
Hi All,
May be you need : Sub DellRows() For r = 1 To 100 ' 100 is end of rows you want If Cells(r, 1).Value = "High" Then del = del & ",A" & r Next r If del < "" Then Range(Mid(del, 2, Len(del))).EntireRow.Delete End Sub Kanga 85 menuliskan: Thanks Dav for your interest. The word "high" will be in column A. It will be a discrete word, but may have other words in the same cell Regards "Dav" wrote: You need to be more specific, would high be an entry in any cell in a row or just say column A. or the first 5 columns. The longer the ranges the longer the macro will take to run. Also would it be in a text string or the entire cell eg how high can you jump or just "high" and if it was the later was for example "highly regarded" would it be retained as well? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560979 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove last word | New Users to Excel | |||
remove rows in one sheet from similar rows in another | Excel Worksheet Functions | |||
how to add word to the front of every word in all rows automatica. | Excel Discussion (Misc queries) | |||
Remove complete row containing a single word | Excel Worksheet Functions | |||
How to remove a word from dictionary? | Excel Discussion (Misc queries) |