Excel - Hiding rows when validation list is changed

Ask a question



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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team