Cell Contains Word

[roo]

Hello,

I am running a script to hide rows if it equals a certian test:

Sub hideit()
For n = 3 To 10
If Cells(n, "B") = "Eng" Then
Cells(n, "B").EntireRow.Hidden = True
Else
Cells(n, "B").EntireRow.Hidden = False
End If
Next n
End Sub

So if it equals "Eng" then hide the row. I also have certian cells that contain more then one word like "Eng 1", "Eng 2", etc. How do you tell it to hide if the string contains "Eng" not just equals? I have tried searching but unable to find a solution.

Tnx

Andrew

[georgiboy]

Try this...

[VBA]Sub hideit()
For n = 3 To 10
If InStr(Cells(n, "B"), "Eng") Then
Cells(n, "B").EntireRow.Hidden = True
Else
Cells(n, "B").EntireRow.Hidden = False
End If
Next n
End Sub[/VBA]

Hope this helps

[roo]

That worked like a charm and yet so simple .

Thanks

[mikerickson]

Constructs like [VBA]If condition then
Variable = True
Else
Variable = False
End if[/VBA]can be replaced with[VBA]Variable = condition
[/VBA]
As in
[VBA]Sub hideit()
For n = 3 To 10
Cells(n, "B").EntireRow.Hidden = CBool(InStr(Cells(n, "B"), "Eng"))
Next n
End Sub [/VBA]

[roo]

Constructs like [vba]If condition then
Variable = True
Else
Variable = False
End if[/vba]can be replaced with[vba]Variable = condition
[/vba] As in
[vba]Sub hideit()
For n = 3 To 10
Cells(n, "B").EntireRow.Hidden = CBool(InStr(Cells(n, "B"), "Eng"))
Next n
End Sub [/vba]

Cool, tnx

Will this run faster then a Case statement?

[mikerickson]

Select Case is one of the slower branching options.
With this construction

If Condition then
   var = True
Else
   var = False
End If

VBA has to
1) calculate Condition
2) test if Condition = True
3) goto the appropriate instruction
4) assign var a value

With[VBA]var = Condition[/VBA]it has to
1) calculate Condition
2) assign var that value

If one can avoid branching ("If" in all of its manifestations, including IIf and constructions like CLng(a=2)) the routine will be faster.