Excel - Hiding rows when validation list is changed

December 2016




Issue


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. </gras>

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.

Solution

  • Implement the following code by right clicking the sheet tab and selecting view code: </souligne>

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.

Note that


Thanks to Trowa for this tip on the forum.

Related :

This document entitled « Excel - Hiding rows when validation list is changed » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.