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

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

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

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

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

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

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

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