Show & Hide Rows - Depending on value (Automatic Change, buttonless)

[JohnHarmeston]

Hey people,

Just started using vba and this site has really helped me! So I thought I'd try asking with this;
I'm wanting to set up a page on excel so that when B15 reads a value, it reveals the corresponding amount of rows on the page.
The Values in B15 are drop box selections of:
- Single
- Multi x2
- Multi x3
etc. through to x10

The code I've pasted below doesn't work on any of the selections apart from the "Single" selection, and hides the right rows. But there's no reaction to any other selected option.

PS - There is probably a much easier way or typing "hide all rows below 'x'" besides typing out 1048576 but i don't know what it is, haha. So anyone is welcome to shorten the code as they find appropriate!


Cheers,

Private Sub Sheet1_Change(ByVal Target As Range)
    Dim rng As Range
        Set rng = Target.Parent.Count("B15")
            If Target.Range = "Single" Then
            Application.Rows("1:25").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("26:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x2" Then
            Application.Rows("1:29").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("30:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x3" Then
            Application.Rows("1:32").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("33:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x4" Then
            Application.Rows("1:35").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("36:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x5" Then
            Application.Rows("1:38").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("39:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x6" Then
            Application.Rows("1:41").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("42:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x7" Then
            Application.Rows("1:44").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("45:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x8" Then
            Application.Rows("1:47").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("48:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x9" Then
            Application.Rows("1:50").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("51:1048576").Select Application.Selection.EntireRow.Hidden = True
            ElseIf Target.Count = "Multi x10" Then
            Application.Rows("1:53").Select Application.Selection.EntireRow.Hidden = False
            Application.Rows("54:1048576").Select Application.Selection.EntireRow.Hidden = True
End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim x As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B15" Then
      Rows("26:" & Rows.Count).Hidden = True
      Select Case Target.Value
         Case "Single"
         Case Else
            x = Split(Target.Value, "x")(1)
            Rows("1:" & 23 + 3 * x).Hidden = False
      End Select
   End If
End Sub

 

[JohnHarmeston]

Thanks for the reply!
　
I've tried using the following (Identical, then again with the named B15 box according to the worksheet name) but it's still only reading if it's the "Single" Selection. The others have zero response.


I'm confused as to why it's only working on the "Single" selection too but not any others! Sorry for this, I've only just been self learning for like 3 weeks.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim x As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "'ID&V (Stub)'!B15" Then
      Rows("26:" & Rows.Count).Hidden = True
      Select Case Target.Value
         Case "Single"
         Case Else
            x = Split(Target.Value, "x")(1)
            Rows("1:" & 23 + 3 * x).Hidden = False
      End Select
   End If
End Sub

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim x As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B15" Then
      Rows("26:" & Rows.Count).Hidden = True
      Select Case Target.Value
         Case "Single"
         Case Else
            x = Split(Target.Value, "x")(1)
            Rows("1:" & 23 + 3 * x).Hidden = False
      End Select
   End If
End Sub

 

[Fluff]

The code needs to be "as-is" & it needs to go in the sheet module for the sheet containing the drop-down

 

[JohnHarmeston]

Ah great! Got it working now. Thanks, I set it up as a separate module instead of putting it in the individual worksheet.

Thanks again!
John

 

[Fluff]

You're welcome & thanks for the feedback