Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Checkboxes on Worksheet | Excel Programming | |||
looping thru the worksheet | Excel Programming | |||
Looping Through Worksheet | Excel Programming | |||
Only works on One Worksheet, When Looping thru All WS | Excel Programming | |||
Looping through charts in a worksheet | Excel Programming |