Previous Topic Next Topic

Hiding rows when validation list is changed

Solved/Closed
Danny - Jan 26, 2010 at 07:35 AM
 tani - Mar 24, 2016 at 11:07 AM
Hello,


I have a excel sheet where have two sets of product line. I would need only details for one set of product line visible if i select it in validation.

For example:

In my sheet have validation drop down in cell a2 and data in A3 to S39 and A42 to S77. when is select select 'X" in validation i should get data in A42 to S77 and other rows(A3 to S39) hidden.If i select rose in cell A2 i need only data for that.

Could any one help?

Thanks

2 replies

Answer 1 / 2
Trowa
Jan 26, 2010 at 08:09 AM
Hi Danny,

Implement the following code by right clicking the sheet tab and selecting view code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("A2") = "X" Then
    Rows("3:39").EntireRow.Hidden = True
    Rows("42:77").EntireRow.Hidden = False
    End If
    If Range("A2") = "Rose" Then
    Rows("42:77").EntireRow.Hidden = True
    Rows("3:39").EntireRow.Hidden = False
    End If
    If Range("A2") = "" Then
    Rows("42:77").EntireRow.Hidden = False
    Rows("3:39").EntireRow.Hidden = False
    End If
  
End Sub


To display all rows again, delete the content of cell A2.

The code will be activated whenever a change to the sheet is made.

Does this suit your needs?

Best regards,
Trowa
0
Answer 2 / 2
tani
Mar 24, 2016 at 11:07 AM
Thanks. It worked great. For some reason, the view/cursor will go to the very bottom of my excel sheet rows 126:127, everytime I choose in A52. It does hide the 59:61, but the view will go to 126:127.



Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A52") = "Port-Side Intake" Then
Rows("59:61").EntireRow.Hidden = True
Else
Rows("59:61").EntireRow.Hidden = False
End If

If Range("A52") = "Port-Side Exhaust" Then
Rows("56:58").EntireRow.Hidden = True
Else
Rows("56:58").EntireRow.Hidden = False
End If

If Range("A122") = "Port-Side Intake" Then
Rows("124:125").EntireRow.Hidden = True
Else
Rows("124:125").EntireRow.Hidden = False
End If

If Range("A122") = "Port-Side Exhaust" Then
Rows("126:127").EntireRow.Hidden = True
Else
Rows("126:127").EntireRow.Hidden = False
End If


End Sub
0