Deleting or Hiding Rows

[Steve]

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 de Bruin]

Hi Steve

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

 

[Tom Ogilvy]

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

 

[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" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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

 

[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" <[email protected]> wrote in

message news:[email protected]...

 

[Tom Ogilvy]

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.

 

[Guest]

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