Code to show/hide rows based on Y

**Justinmih** · 05-03-2011, 05:15 PM

Hi there,

I am working on a department tracker that allows the user to select the departments in the store by using the letter "y" in column C in the 'Codes' sheet. All departments that are selected with a Y then remains visible on the 'training' sheet. All other departments that are not selected with a "Y" will remain blank and hidden on the 'Training' sheet.
The code would need to identify the line based on the Dept code displayed in each of the sheets(example 015a)

I have found a piece of code that I have been trying to adapt to fit my sheet however as i have little experience in VBA i am finding it difficult.

Please see code below

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Cells.Count > 1 Then Exit Sub
  
  If Not Intersect(Target, Range("code!c2:c440")) Is Nothing Then
     Select Case UCase(Target)
       Case Is = ""","","
         Target.EntireRow.Hidden = True
       Case Is = "Y"
         Target.EntireRow.Hidden = False
      End Select
  End If
  
End Sub

If anyone could help it would be greatly appeciated.

Thanks

Justin

**TMS** · 05-03-2011, 05:19 PM

Please add code tags to your original post before any forum member can provide assistance.

Regards

**barryleajo** · 05-03-2011, 05:29 PM

Hi Justin
These mods get your code working:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Cells.Count > 1 Then Exit Sub
  
  If Not Intersect(Target, Worksheets("Codes").Range("c2:c440")) Is Nothing Then
     Select Case UCase(Target)
       Case Is = ""","","
         Target.EntireRow.Hidden = False
       Case Is = "Y"
         Target.EntireRow.Hidden = True
      End Select
  End If
  
End Sub

By the way the protocol on this forum is to wrap your code in Code Tags

**Justinmih** · 05-03-2011, 05:52 PM

Hi Barry,
Thanks for the speedy reply.
I amended the post to fix the code and also added another important piece of info, sorry I wasn't quick enough to catch the post in time!

I needed the code to:-
If the department is selected "y" in the 'codes' sheet then the line remains visible in the 'Training' sheet. all other lines without a "Y" need to be hidden on the 'Training' sheet.
Also I need to the rows to be hidden based on the dept code not the row it sit on.

Thanks again and sorry for wasting your time before.

Regards

Justin.

**barryleajo** · 05-03-2011, 05:55 PM

Thats no problem Justin
Glad your sorted

**Justinmih** · 05-03-2011, 06:12 PM

Sorry Barry,
The info i gave you in the first post was incomplete.
If you could help me amend the code to include the following that would be great?

needed the code to:-
If the department is selected "y" in the 'codes' sheet then the line remains visible in the 'Training' sheet. all other lines without a "Y" need to be hidden on the 'Training' sheet.
Also I need to the rows to be hidden based on the dept code not the row it sit on.

Thanks

Justin.