Looping over a worksheet
 

Hello. I'd like to loop over all rows in a worksheet, and hide those
matching certain criteria.

The criteria a
1. Cell in column 'H' equals "PASS"
2. Cell in column 'M' contains the strings "POUT" and "DIFF"

I have so far:

Sub show_diffs()
'
' Hide all rows except those showing POUT and DIFF
' except for rows with FAIL
'
Application.Goto Range("A2")
ActiveCell.EntireRow.Select
Do While ActiveCell.Value < ""
if selection ????????
Selection.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).EntireRow.Select
Loop
End Sub

Thanks.

Stephen

Looping over a worksheet
 

Sub show_diffs()
Dim i As Long
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i,0).Value < ""
If .Offset(i,7).Value = "PASS" AND _
(.Offset(i,12).Value = "POUT" OR .Offset(i,12).Value =
"DIFF") Then
.Offset(i,0).EntireRow.Hidden = False
End If
i = i + 1
Loop
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hello. I'd like to loop over all rows in a worksheet, and hide those
matching certain criteria.

The criteria a
1. Cell in column 'H' equals "PASS"
2. Cell in column 'M' contains the strings "POUT" and "DIFF"

I have so far:

Sub show_diffs()
'
' Hide all rows except those showing POUT and DIFF
' except for rows with FAIL
'
Application.Goto Range("A2")
ActiveCell.EntireRow.Select
Do While ActiveCell.Value < ""
if selection ????????
Selection.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).EntireRow.Select
Loop
End Sub

Thanks.

Stephen

Looping over a worksheet
 

One way:

Sub Macro2()
For Each cell In Range("M1:M100")
If cell.Value = "POUT" _
Then
GoTo Bypass
Else
If cell.Value = "DIFF" _
Then
GoTo Bypass
Else
cell.EntireRow.Hidden = True
End If
End If
Bypass:
Next cell

End Sub


Regards,
Paul


wrote in message
oups.com...
Hello. I'd like to loop over all rows in a worksheet, and hide those
matching certain criteria.

The criteria a
1. Cell in column 'H' equals "PASS"
2. Cell in column 'M' contains the strings "POUT" and "DIFF"

I have so far:

Sub show_diffs()
'
' Hide all rows except those showing POUT and DIFF
' except for rows with FAIL
'
Application.Goto Range("A2")
ActiveCell.EntireRow.Select
Do While ActiveCell.Value < ""
if selection ????????
Selection.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).EntireRow.Select
Loop
End Sub

Thanks.

Stephen

Looping over a worksheet
 

Thanks to you both for the ideas. That takes care of the looping part.

Are there any string functions that tell whether a substring is in a
string? Is this valid:

If "Joe" in "Joe was here" then
....
Endif
?

Stephen

Bob Phillips wrote:
Sub show_diffs()
Dim i As Long
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i,0).Value < ""
If .Offset(i,7).Value = "PASS" AND _
(.Offset(i,12).Value = "POUT" OR .Offset(i,12).Value =
"DIFF") Then
.Offset(i,0).EntireRow.Hidden = False
End If
i = i + 1
Loop
End With
End Sub


--
HTH

Bob Phillips

Looping over a worksheet
 

Thanks to everyone's help, I've gotten this far.

I'm receiving a "Loop without Do" error that I don't quite understand:

Sub show_diffs()
Dim Substring1, Substring2, FindinString, MyPos
Dim i As Long
Substring1 = "POUT"
Substring2 = "DIFF"

i = 1
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i, 0).Value < ""
.Offset(i, 0).EntireRow.Hidden = True
FindinString = .Offset(i, 12).Value
If InStr(FindinString, Substring1) Then
If InStr(FindinString, Substring2) Then
.Offset(i, 0).EntireRow.Hidden = False
End If
End If
If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1
Loop
End With
End Sub

Looping over a worksheet
 

Looks like you have a block if statement that is not ended.

If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1

I'm not sure what you wanted to do here. You can either add an underscore "
_" next to Then, or you'll need to place an "End If" somewhere. Either
above or below the "i= i + 1".

HTH,
Paul


wrote in message
oups.com...
Thanks to everyone's help, I've gotten this far.

I'm receiving a "Loop without Do" error that I don't quite understand:

Sub show_diffs()
Dim Substring1, Substring2, FindinString, MyPos
Dim i As Long
Substring1 = "POUT"
Substring2 = "DIFF"

i = 1
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i, 0).Value < ""
.Offset(i, 0).EntireRow.Hidden = True
FindinString = .Offset(i, 12).Value
If InStr(FindinString, Substring1) Then
If InStr(FindinString, Substring2) Then
.Offset(i, 0).EntireRow.Hidden = False
End If
End If
If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1
Loop
End With
End Sub

Looping over a worksheet
 

If "Joe in here " like "*Joe*

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Thanks to you both for the ideas. That takes care of the looping part.

Are there any string functions that tell whether a substring is in a
string? Is this valid:

If "Joe" in "Joe was here" then
....
Endif
?

Stephen

Bob Phillips wrote:
Sub show_diffs()
Dim i As Long
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i,0).Value < ""
If .Offset(i,7).Value = "PASS" AND _
(.Offset(i,12).Value = "POUT" OR .Offset(i,12).Value =
"DIFF") Then
.Offset(i,0).EntireRow.Hidden = False
End If
i = i + 1
Loop
End With
End Sub


--
HTH

Bob Phillips

Looping over a worksheet
 

These two lines


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False

should be three


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
End If


and you should not set i = 1 before the loop, let it default to 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Thanks to everyone's help, I've gotten this far.

I'm receiving a "Loop without Do" error that I don't quite understand:

Sub show_diffs()
Dim Substring1, Substring2, FindinString, MyPos
Dim i As Long
Substring1 = "POUT"
Substring2 = "DIFF"

i = 1
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i, 0).Value < ""
.Offset(i, 0).EntireRow.Hidden = True
FindinString = .Offset(i, 12).Value
If InStr(FindinString, Substring1) Then
If InStr(FindinString, Substring2) Then
.Offset(i, 0).EntireRow.Hidden = False
End If
End If
If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1
Loop
End With
End Sub

Looping over a worksheet
 

Bob Phillips wrote:
These two lines


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False

should be three


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
End If

Thanks! That did the trick. Lots to learn ...

Stephen