Hiding rows when validation list is changed [Solved/Closed]

Question

Hiding rows when validation list is changed [Solved/Closed]

Report

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

Hiding rows when validation list is changed
Excel - Compare two lists and change row color - How-To - Excel
Compare two lists and change row colour ✓ - Forum - Office Software
Excel data validation list from table multiple columns - How-To - Excel
Excel - Hide rows with zero value in Multiple sheets - How-To - Excel
Excel - Set default width for validation lists - How-To - Excel

2 replies

Ask a question

Trowa · Answer 1 · 2010-01-26T08:09:56-0500

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 2 · 2016-03-24T11:07:42-0400

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 [Solved/Closed]

2 replies

Your reply

Subscribe To Our Newsletter!