Hiding rows when validation list is changedSolved/Closed

Question

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

Trowa · Answer

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

tani · Answer

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

Hiding rows when validation list is changed

2 responses

Similar discussions

Subscribe To Our Newsletter!