Prev: restrict a product eg 10*5=40 because 40 is maximum
Next: Auto_Open over Private Sub Workbook_Open()
From: PPL on 29 May 2010 13:01
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





From: Don Guillett on 29 May 2010 13:17
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"PPL" <pp1(a)shawRemoveThis.ca> wrote in message
news:9MbMn.19344$yx.12498(a)newsfe13.iad...
> 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
>
>
>
>
>

From: Per Jessen on 29 May 2010 13:25
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" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen
news:9MbMn.19344$yx.12498(a)newsfe13.iad...
> 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
>
>
>
>
>
From: PPL on 29 May 2010 14:24
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



"Per Jessen" <per.jessen(a)mail.dk> wrote in message
news:eCAwkP1$KHA.4308(a)TK2MSFTNGP04.phx.gbl...
> 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" <pp1(a)shawRemoveThis.ca> skrev i meddelelsen
> news:9MbMn.19344$yx.12498(a)newsfe13.iad...
>> 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
>>
>>
>>
>>
>>


From: PPL on 29 May 2010 14:33
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


"Don Guillett" <dguillett1(a)gmail.com> wrote in message
news:e8gRWL1$KHA.1892(a)TK2MSFTNGP05.phx.gbl...
> 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
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "PPL" <pp1(a)shawRemoveThis.ca> wrote in message
> news:9MbMn.19344$yx.12498(a)newsfe13.iad...
>> 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
>>
>>
>>
>>
>>
>


 |  Next  |  Last
Pages: 1 2 3
Prev: restrict a product eg 10*5=40 because 40 is maximum
Next: Auto_Open over Private Sub Workbook_Open()