PDA

View Full Version : Cell Contains Word



roo
01-14-2009, 04:47 AM
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
01-14-2009, 05:04 AM
Try this...

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

Hope this helps

roo
01-14-2009, 05:12 AM
That worked like a charm and yet so simple :rotlaugh: .

Thanks

mikerickson
01-14-2009, 07:45 AM
Constructs like If condition then
Variable = True
Else
Variable = False
End ifcan be replaced withVariable = condition

As in
Sub hideit()
For n = 3 To 10
Cells(n, "B").EntireRow.Hidden = CBool(InStr(Cells(n, "B"), "Eng"))
Next n
End Sub

roo
01-15-2009, 12:42 PM
Constructs like If condition then
Variable = True
Else
Variable = False
End ifcan be replaced withVariable = condition
As in
Sub hideit()
For n = 3 To 10
Cells(n, "B").EntireRow.Hidden = CBool(InStr(Cells(n, "B"), "Eng"))
Next n
End Sub

Cool, tnx

Will this run faster then a Case statement?

mikerickson
01-15-2009, 01:14 PM
Select Case is one of the slower branching options.
With this construction

If Condition then
var = True
Else
var = False
End IfVBA has to
1) calculate Condition
2) test if Condition = True
3) goto the appropriate instruction
4) assign var a value

Withvar = Conditionit 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.