Hiding/Showing Rows

[AshParr]

This may seem a little easy to some of you guys, but i am just
starting to lean about excel and vba. So i though i would ask for some
help.

======================================================================

I currently have on a sheet a list of names from B6 to B43 and then in
the next column i have either a yes or no from C6 to C43.(The Yes & No
Are determined from another sheet)

Is there any way that i can hide all of the rows that contain Yes and
only show the rows containing No?

Then Is there a way to make sure this keeps occuring so that if i did
change the cell from a yes to a no that would be hidden and vice versa
if i changed a no to a yes would that then show?

This is what i thought could do it but to no success:

Private Sub Worksheet_Activate()

If C6 = No Then
Rows("6:6").Select
Selection.EntireRow.Hidden = True
End If

If C6 = Yes Then
Rows("6:6").Select
Selection.EntireRow.Hidden = False
End If

End Sub

Any Replies Would Be Appreciated,
Thankyou in advance

Ash

 

[Don Guillett]

You can use data>filter>autofilter>filter on it >hide Record a macro
OR
for each c in range("b6:b43")
if Ucase(c)="Y" then c.entirerow.hidden=true
next c

 

[AshParr]

Thankyou for the response but using auto filter i get the arrows at
the top of the cell or can i remove that?

Also i dont understand this part:
for each c in range("b6:b43")
if Ucase(c)="Y" then c.entirerow.hidden=true
next c

 

[Guest]

Private Sub Worksheet_Activate()
Dim c as Range
for each c in me.range("C6:C43")
if Ucase(c)="Y" then
c.Entirerow.hidden = True
Else
c.EntireRow.hidden = False
End if
next c

End Sub

is what I believe Don was alluding to.

 

[AshParr]

Thankyou to both of you for the replies but it doesnt seem to work.
ill explain a little more:

In Cell C6 To C43 I have This:
='Overdue Review'!A4
'
='Overdue Review'!43
Which basicly looks at a different sheet in my workbook, it is either
a yes or a no.

If this cell equals No then i want it to hide,
If this cell equals Yes then i want it to be shown,

And i was wondering could this be done?

 

[Guest]

You indicated in your original post that you wanted to use the sheet activate
event.

to make the code execute, you would have to select another sheet, then come
back to the sheet where you want the rows to be hidden (the sheet with the
code). (you did put the code in the sheet module, correct).

If you want it to happen every time there is a calculate, then put the code
in the calculate event for the sheet where you want the rows hidden.

Anyway, as expected, it worked fine for me.