Remove all rows which do Not contain a particular word

[Kanga 85]

I need a program which will remove from a largs WorkSheet all rows if the
word "High' is not present in the row.
Thanks

[Dav]

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

[Kanga 85]

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]

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

[Dav]

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

[Kanga 85]

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
>
>

[Kanga 85]

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
>
>

[halim]

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
> >
> >