Is there a way to delete or hide a set of rows in a range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve

Hi Steve

Do you want to check one column or the whole row for the specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in message ...
Is there a way to delete or hide a set of rows in a range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve

Dim rng as Range, cell as Range
set rng = Range("A1:A100")
rng.EntireRow.Hidden = False
for each cell in Rng
if instr(1,cell,"ABCD",vbTextcompare) = 0 then
cell.Entirerow.Hidden = True
End if
Next

if the string could be anywhere in the row
Dim rng as Range, cell as Range
set rng = Range("A1:A100")
rng.EntireRow.Hidden = False
for each cell in Rng
if application.Countif(cell.EntireRow,"*abcd*") = 0 then
cell.Entirerow.Hidden = True
End if
Next
--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
Is there a way to delete or hide a set of rows in a range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve

The whole row.
-----Original Message-----
Hi Steve

Do you want to check one column or the whole row for the
specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
Is there a way to delete or hide a set of rows in a
range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve


.

For row 1 -100 try this then

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow), _
"ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ron" not exist in the row
'(It will look in the whole row)

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in message ...
The whole row.
-----Original Message-----
Hi Steve

Do you want to check one column or the whole row for the
specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
Is there a way to delete or hide a set of rows in a
range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve


.

Ron,

It is deleting all the rows and not leaving the rows I
need.
-----Original Message-----
For row 1 -100 try this then

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If Application.WorksheetFunction.CountIf(.Rows
(Lrow), _
"ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ron" not
exist in the row
'(It will look in the whole row)

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
The whole row.
-----Original Message-----
Hi Steve

Do you want to check one column or the whole row for
the
specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
Is there a way to delete or hide a set of rows in a
range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve


.



.

That is because it is looking for a cell that contains Ron and only Ron.

If the cell contains Ron as part of a sentence, then that row goes.

You can look for Ron as a string segment with

If Application.WorksheetFunction.CountIf(.Rows(Lrow), _
"*ron*") = 0 Then .Rows(Lrow).Delete

but that would keep RobotRon

Perhaps more explanation on what determines if a row stays.

--
Regards,
Tom Ogilvy


"Steve" wrote in message
...
Ron,

It is deleting all the rows and not leaving the rows I
need.
-----Original Message-----
For row 1 -100 try this then

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If Application.WorksheetFunction.CountIf(.Rows
(Lrow), _
"ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ron" not
exist in the row
'(It will look in the whole row)

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
The whole row.
-----Original Message-----
Hi Steve

Do you want to check one column or the whole row for
the
specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
Is there a way to delete or hide a set of rows in a
range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve


.



.

Your explanation made perfect sense. I tweaked the search
string a bit and now it works perfectly. Thanks guys. I
appreciate all the help.

Steve
-----Original Message-----
That is because it is looking for a cell that contains
Ron and only Ron.

If the cell contains Ron as part of a sentence, then that
row goes.

You can look for Ron as a string segment with

If Application.WorksheetFunction.CountIf(.Rows(Lrow), _
"*ron*") = 0 Then .Rows(Lrow).Delete

but that would keep RobotRon

Perhaps more explanation on what determines if a row
stays.

--
Regards,
Tom Ogilvy


"Steve" wrote in
message
...
Ron,

It is deleting all the rows and not leaving the rows I
need.
-----Original Message-----
For row 1 -100 try this then

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1

If Application.WorksheetFunction.CountIf
(.Rows
(Lrow), _
"ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ron" not
exist in the row
'(It will look in the whole row)

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote in
message ...
The whole row.
-----Original Message-----
Hi Steve

Do you want to check one column or the whole row for
the
specific word

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" wrote
in
message news:52c501c489f8$b151cf80
...
Is there a way to delete or hide a set of rows in
a
range,
if a specific word doesn't appear?

Any help is appreciated as always,
Steve


.



.



.