Specifying a string in a range

[SV]

Hi,

I'm using this function to help me count and hide rows in my sheet,

If G5 = True And sht.Name = "Patient Summary" Then

lastRow = sht.UsedRange.Rows.Count + sht.UsedRange.Row - 1

For i = 1 To lastRow
If sht.Range("A" & i) = "US" Then
sht.Rows(i & ":" & i + 8).EntireRow.Hidden = True
End If
Next i

But i'm hitting a road block in terms of specifying a cell on this
SAME SHEET which has more than the word "US" for example if it says
"Drug treated US" or "Incidence US" etc, then how do i tackle the
issue especially when its all on the same sheet. Is it something to do
with string function?


Any help would be greatly appreciated,

thanks!!!
SV

 

[Norman Jones]

Hi SV,

Try replacing:

If sht.Range("A" & i) = "US" Then

with

If InStr(1, sht.Range("A" & i).Value, "US", vbTextCompare) Then

 

[SV]

Hi Norman,

Thanks for replying, can you also spell what the function means, sorry
amstilla novice wiht VB...

Thanks again!
SV

 

[Norman Jones]

Hi SV,


'-------------
Thanks for replying, can you also spell what the function means, sorry
amstilla novice wiht VB...
'-------------

See the Instr function in VBA help.

However, the function returns the position of a substring
(your search string 'US') within another string - in your case,
the cell value.

The first parameter specifies the start position of the search;
the second argument specifies the string to be searched; the
third specifies the search string ad the last argument defines
the search method; in this instance the search is case insensitive.

The If construct employed results in an implicit boolean
conversion of the function result: a result of zero (not found) is
returned as False; any other value is returned as True.

 

[SV]

It WORKS!! Thanks so much Norman!!!

SV