Dropdown to hide/show rows

[paxile2k]

I have a cell that is using data validation using the numbers 1-15, based on the number in that box i want to show that number of rows, the default is 1 so lets say row will be visable, if the drop down changes to say 3, rows 3,4, and 5 will be visable. The closest i have gotten is autofilter but does not show the rows, just a row at a time. I dont want the dropdown to add rows, just hide/unhide them based on the dropdown number. Is this possible?

[martindwilson]

dont quite follow if in your example 15 was chosen do you want to show 1-15 or 15-29. cant see the logic of 3 show 3-5

[paxile2k]

Thankyou for your response,

I have cell A2 setup to use a drop down with the numbers 1-15, i would like that drop to hide/show rows 3-17, if the drop down is changed to 3, rows 3,4 and 5 will be visable and the others hidden, if the drop down is changed to 7, rows 3-10 will be visable and the others below it will be hidden. I was using 3 as an example as what is chosen from the dropdown menu.

[JBeaucaire]

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Rows("3:17").Hidden = False
        For i = 1 To Range("A2").Value
            Rows(i + 2).Hidden = True
        Next i
    End If
End Sub

[paxile2k]

thankyou for your response,

Thankyou for the code, i tweaked a little bit, only issue is that with the number 2 being chosen it should show 2 rows but it only hows the same as if 1 were chosen.

Here is the code i am using from your example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
Rows("4:17").Hidden = True
For i = 1 To Range("A2").Value
Rows(i + 1).Hidden = False
Next i
End If
End Sub

Correction: everything is offset by 1, if you select 1, nothing appears, if you select 2, one row appears, if you select 3, 2 rows appear, and so on

[paxile2k]

Nevermind i adjusted it incorrectly, thankyou verymuch for your assistance It was greatly appreciated

[JBeaucaire]

Can you please edit your post #5 above to wrap the code in [code] tags as per forum rules? Or just delete the code.

Thanks.

[paxile2k]

Is there a way to have two of these options in one spreadsheet? I have spreadsheet where it is working fine, but i am trying to add another hide a different set of rows? Does the code need a different name?

Example of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A9")) Is Nothing Then
        Rows("11:24").Hidden = True
        For i = 1 To Range("A9").Value
            Rows(i + 9).Hidden = False
        Next i
    End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A28")) Is Nothing Then
        Rows("30:24").Hidden = True
        For i = 1 To Range("A28").Value
            Rows(i + 29).Hidden = False
        Next i
    End If
End Sub

[JBeaucaire]

I know it looks like your question is related, but it's actually a completely different topic. Forum rules make posting add-on questions in someone else's thread a no-no...so remember that next time, OK.

Just start your own thread and include links to other threads you "think" might be relevant.

In this instance, your own question is simply: "Can you have more than one Worksheet_Change macro in one sheet?" If you Googled that question, you'd find immediately the answer is no.

But you can make the one ws_change event do multiple things.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("A9")) Is Nothing Then
        Rows("11:24").Hidden = True
        For i = 1 To Range("A9").Value
            Rows(i + 9).Hidden = False
        Next i
    End If

    If Not Intersect(Target, Range("A28")) Is Nothing Then
        Rows("30:24").Hidden = True
        For i = 1 To Range("A28").Value
            Rows(i + 29).Hidden = False
        Next i
    End If
End Sub