Deleting or Hiding Rows
 

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

Deleting or Hiding Rows
 

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

Deleting or Hiding Rows
 

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

Deleting or Hiding Rows
 

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


.

Deleting or Hiding Rows
 

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


.

Deleting or Hiding Rows
 

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


.



.

Deleting or Hiding Rows
 

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


.



.

Deleting or Hiding Rows
 

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


.



.



.