Hiding rows based on input parameter

[Niss1]

In my excel sheet I have a drop down box.
If the user selects "Parameter1" i would like to unhide the next 5 rows, if he chooses something else then i would like to hide those 5 rows.

This happens several times in my worksheet and i would like to have something where i do not need to hardcode the row numbers for each case.

 

[Andrew Poulsom]

Welcome to MrExcel.

What sort of dropdown is it - Data Validation, ActiveX or Forms control?

 

[Niss1]

Hey and thank you!

Its a data validation dropdown box

 

[Weaver]

How are you populating the dropdown, is it from a range?

 

[Andrew Poulsom]

Try this in the module for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$A$1" Then
        With Target
            .Offset(1).Resize(5).EntireRow.Hidden = .Value <> "Parameter1"
        End With
    End If
    Application.EnableEvents = True
End Sub

Change the reference to A1 to suit.

 

[Niss1]

Thank you for a fast reply!

This works perfect for 1 cell/value.

Is there any way to make this applicable for the entire sheet(for every time there is a "Parameter1)" or do i have to hardcode this to apply for every single cell where i have this dropbox?

 

[Weaver]

If the dropboxes are populated from ranges, I think we could make it work over the full sheet without any hard coding.

 

[Niss1]

the dropboxes pick their data the same range

 

[Andrew Poulsom]

For A1 and A7:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$A$1" Then
        With Target
            .Offset(1).Resize(5).EntireRow.Hidden = .Value <> "Parameter1"
        End With
    ElseIf Target.Address = "$A$7" Then
        With Target
            .Offset(1).Resize(5).EntireRow.Hidden = .Value <> "Parameter1"
        End With
    End If
    Application.EnableEvents = True
End Sub

Add more ElseIfs if necessary.

 

[Niss1]

Thanks again

This solves the problem