Excel - Hiding rows when validation list is changed

May 2017




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


Published by aakai1056. Latest update on September 26, 2011 at 08:26 AM by aakai1056.
This document, titled "Excel - Hiding rows when validation list is changed," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).