Problem with the "Or" operand in Excel

[PPL]

Excel 2002/3
I am trying to write a short script that looks at all cells in column C and
if the cell contains a selected name (i.e., Fred or John or Mary) then the
entire row is hidden
I would have thought that the following would have doen it?

Sub MyHideRows()
Dim startrow As Integer
startrow = 1
Do Until startrow > Cells(Cells.Rows.Count, "C").End(xlUp).Row
If Cells(startrow, 3).Value <> "Fred" _
Or Cells(startrow, 3).Value <> "John" _
Or Cells(startrow, 3).Value <> "Mary" Then

Cells(startrow, 3).Select
Selection.EntireRow.Hidden = True
End If

startrow = startrow + 1
Loop
End Sub

The result is that regardless of content the script hides all rows

Interstingly if I strip out the "Or" operands and leave the basic "If"
statement it works fine.
So this works:

If Cells(startrow, 3).Value <> "Fred" Then
Cells(startrow, 3).Select
Selection.EntireRow.Hidden = True
End If

Can somebody please please help me with the logic here (or my lack of same!)

TIA

Phil

 

[Don Guillett]

You were saying <> which is NOT equal instead of = Also, work from the
bottom up

Sub MyHideRowsSAS()
Rows.Hidden = False
For i = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If LCase(Cells(i, 3)) = "fred" Or _
LCase(Cells(i, 3)) = "john" Or _
LCase(Cells(i, 3)) = "mary" Then Rows(i).Hidden = True
Next i
End Sub

 

[Per Jessen]

Hi

This will hide all rows which do not contain one of the names.

Sub MyHideRows()
Dim StartRow As Integer
StartRow = 1
Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row
If Cells(StartRow, 3).Value <> "Fred" _
And Cells(StartRow, 3).Value <> "John" _
And Cells(StartRow, 3).Value <> "Mary" Then

Rows(StartRow).Hidden = True
End If

StartRow = StartRow + 1
Loop
End Sub

And this will hide rows which contain one of the selected names:

Sub MyHideRows()
Dim StartRow As Integer
StartRow = 1
Do Until StartRow > Cells(Cells.Rows.Count, "C").End(xlUp).Row
If Cells(StartRow, 3).Value = "Fred" _
Or Cells(StartRow, 3).Value = "John" _
Or Cells(StartRow, 3).Value = "Mary" Then

Rows(StartRow).Hidden = True
End If

StartRow = StartRow + 1
Loop
End Sub

Regards,
Per

 

[PPL]

Hi Per,
Thank you so much for this - it works, and sorry for the confusion in my
earlier post, I am actually trying to hide all rows apart from those that
contain Fred, or John or Mary

You have used the "<>" plus the "And" operand and it works perfectly thank
you. Problem is I don't understand why. In my (obviously wrong) logic the Or
operand should have done the job - can you explain why it doesn't. Again in
my twisted logic I'd have said that inorder for the "If" statement to work
with the "And" operand then the cell being tested would have to contain ALL
three names!

Thank you again for your help. Much appreciated!

Phil

 

[PPL]

Hi Don,
Thanks for this,
My mistake for the confusion. I'm trying to hide all rows that do not
contain one of the three names (Fred or John or Mary)
Why does working from the bottom up make a difference?

Thanks again
Phil

 

[Per Jessen]

Phil,

Thanks for your reply.

Let's look at the logic. All parts of my statement (using AND) has to be
true for the entire If statement to evaluate true.

Using OR will return true if just one part of the statement is true.

Hopes this helps.
....
Per

 

[Dave Peterson]

Sometimes, you can use a different branching instruction instead of if:

do until...
select case lcase(cells(startrow,3).value)
case is = "fred", "john", "mary"
rows(startrow).hidden = true
case else
rows(startrow).hidden = false
end select

 

[PPL]

Cool,
Thanks for that Dave
Phil

Sometimes, you can use a different branching instruction instead of if:

do until...
select case lcase(cells(startrow,3).value)
case is = "fred", "john", "mary"
rows(startrow).hidden = true
case else
rows(startrow).hidden = false
end select

 

[PPL]

Thanks for repying Per, I appreciate it
So here's my problem, let me step through the logic:
foucus is placed on each cell
The contents is examined
The "If" conditional is excercised to differentiate TRUE from FALSE against
defined arguments
In using the AND operand, we are going test the cell to see if it contains
all three names: Fred AND John AND Mary. If it does then the condition is
TRUE
Well that's not what we want:. The cell contains one name and one name only
which may or may not be Fred, John or Mary, so therefore we should use the
OR operand.

Clearly my logic is unsound becasue it doesn't work that way.
Hope this makes sense ...
TIA

Phil

 

[Dave Peterson]

I have that same problem with the And/or =,<> stuff.

So I try to make it easy for me. I'll change my then/elses around to do what I
want:

if cell.value = "john" _
or cell.value = "fred" _
or cell.value = "mary" then
'do what needs to be done if it's one of these
else
'do what needs to be done if it's not one of these.
end if

Sometimes, my code will look like:

if cell.value = "john" _
or cell.value = "fred" _
or cell.value = "mary" then
'do nothing
else
cell.offset(0,1).value = "not john, fred or mary"
end if

 

[BillyBob]

PPL,

You are not understanding the logic of the OR and AND operators. Each
individual expression it evaluated to TRUE or FALSE independent of the other
expressions.

Logic:

OR Operator
*Only one* expression must evaluate to TRUE for it to be TRUE

AND Operator
*All* the expressions must evaluate to TRUE for it to be TRUE

Let's look at your original if statement :

If Cells(startrow, 3).Value <> "Fred" _
Or Cells(startrow, 3).Value <> "John" _
Or Cells(startrow, 3).Value <> "Mary" Then
Cells(startrow, 3).Select
Selection.EntireRow.Hidden = True
End If

It is interpreted as:

If the value in the indicated cell is NOT "Fred" OR
the value in the indicated cell is NOT "John" OR
the value in the indicated cell is NOT "Mary" then
hide the selected row

This will always evaluate to TRUE because the value in the cell will always
NOT be one of those. If the value is "Fred" then it is TRUE that it is not
"John" or "Mary". If the value is "John" then it TRUE that it is not "Fred"
or "Mary". If the value is "Mary" then it is TRUE it is not "John" or
"Fred". If any value is TRUE then the If statement evaluates to TRUE and
will hide the row. So as you can see, it is ALWAYS TRUE that it will not be
Fred, John or Mary because the value in the cell cannot be all those names
at the same time.

Let's look at Don Guillett's solution:
He kept the OR operator and changed the <> to =

If Cells(startrow, 3).Value = "Fred" _
Or Cells(startrow, 3).Value = "John" _
Or Cells(startrow, 3).Value = "Mary" Then
Cells(startrow, 3).Select
Selection.EntireRow.Hidden = True
End If

If the value is "Fred" then it is TRUE. If the value is "John" then is
TRUE. If the value is "Mary" then it is TRUE. If the value is not Fred,
John or Mary, then none of the expressions are TRUE, so the If evaluates to
FALSE and will not hide the row. It only hides the row if any expression is
TRUE and the only way this can happen is if Fred OR John OR Mary is the cell
value.

Let's look at Per Jessen's solution:
He changed the OR to AND and kept the <>

If Cells(startrow, 3).Value <> "Fred" _
AND Cells(startrow, 3).Value <> "John" _
AND Cells(startrow, 3).Value <> "Mary" Then
Cells(startrow, 3).Select
Selection.EntireRow.Hidden = True
End If

If the value is "Fred" then it is FALSE that it is not Fred. If the value
is "John" then is FALSE that it is not John. If the value is "Mary" then it
is FALSE that it is not Mary. If the value is not Fred, John or Mary, then
it is TRUE that it is not Fred AND it is TRUE that it is not John AND it is
TRUE that it is not Mary. So, if the value is NOT Fred, John or Mary, then
the If evaluates to TRUE because ALL the expressions are TRUE and will hide
the row. It only hides the row if ALL expressions are TRUE.

Hopefully you can see how these logical operators are evaluated. I would
suggest opening Visual Basic --> Help and searching "Logical Operators"
(without the quotes).

BB

 

[PPL]

Hi BillyBob,
That's really really helpful.
Thank you.
I have a much better insight into how it works
Thanks again

Phil

 

[PPL]

Thanks Dave,
Your's together with BillyBob's reply has really helped m to understand this
stuff.
Much appreciated

Thanks again
Phil