How to hide/show cells depending on selection from drop down box?

[swhitesides]

I want to display a selection in a drop down box (1-15). If "1" is chosen then rows 11, 27, & 43 should be displayed. If "2" is chosen then rows 11-12, 27-28, & 43-44 should be displayed. If "3" is chosen then rows 11-13, 27-29, & 43-45 should be displayed. If "4" is chosen then rows 11-14, 27-30, & 43-46 should be displayed. All the way to if "15" is chosen then rows 11-25, 27-41 & 43-57 is displayed. I thnk it would be a macro, I'm just not sure how to make this work. Any suggestions would be greatly appreciated!

 

[SteveO59L]

How are you creating your "drop-down box" ?

 

[swhitesides]

Using data validation.

 

[SteveO59L]

Assuming your data validation cell is D1.

Try something along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 4 Then
If Target.Value = 1 Then
Rows("12:26").Hidden = True
Rows("28:42").Hidden = True
Rows("44:100").Hidden = True
Else
Rows("12:100").Hidden = False

End If
End If

 

[jbeaucaire]

Let's assume the data validation cell is D1.
Right-click the sheetname and select VIEW CODE, paste in this sheet-level macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
    If Not Intersect(Target, Range("D1")) Is Nothing Then
        Range("A11:A57").EntireRow.Hidden = True
        r = Target.Value - 1
        Range("A11:A" & 11 + r).EntireRow.Hidden = False
        Range("A27:A" & 27 + r).EntireRow.Hidden = False
        Range("A43:A" & 43 + r).EntireRow.Hidden = False
    End If
End Sub

Or this, shorter, but harder to read:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
    If Not Intersect(Target, Range("D1")) Is Nothing Then
        Range("A11:A57").EntireRow.Hidden = True
        r = Target.Value - 1
        Range("A11:A" & 11 + r & ",A27:A" & 27 + r & ",A43:A" & 43 + r).EntireRow.Hidden = False
    End If
End Sub

The close the VBEditor and save your sheet. Now any change to D1 should change the visible rows.