Macro the hides entire rows based on a drop down list

[MGK086]

First, thank you for any help you can give. I'm not the best at creating macros, so I need help with this one.

I have a drop down list in cell I3 with a range of numbers from 0-8. If 0 is selected I want to hide rows 14-69.

If 1 is selected hide rows 21-69
If 2 is selected hide rows 28-69
If 3 is selected hide rows 35-69
If 4 is selected hide rows 42-69
If 5 is selected hide rows 49-69
If 6 is selected hide rows 56-69
If 7 is selected hide rows 63-69
If 8 is selected hide rows No hidden Rows

I also need this macro to work to work it the worksheet is coppied into the same workboob multiple times. Is this possible? Thank you for your help!

[Sniper]

Does does help?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("I3")) Is Nothing Then
        Application.EnableEvents = False
        
        If Target.Value = 1 Then
      Rows("21:69").EntireRow.Hidden = True
      End If
      
      If Target.Value = 2 Then
      Rows("28:69").EntireRow.Hidden = True
      Rows("21:27").EntireRow.Hidden = False
       End If
       
        
        If Target.Value = 3 Then
      Rows("35:69").EntireRow.Hidden = True
      Rows("21:34").EntireRow.Hidden = False
       End If
       
        If Target.Value = 4 Then
      Rows("42:69").EntireRow.Hidden = True
      Rows("21:41").EntireRow.Hidden = False
       End If
       
        If Target.Value = 5 Then
      Rows("49:69").EntireRow.Hidden = True
      Rows("21:48").EntireRow.Hidden = False
       End If
       
        If Target.Value = 6 Then
      Rows("56:69").EntireRow.Hidden = True
      Rows("21:55").EntireRow.Hidden = False
       End If
       
        If Target.Value = 7 Then
      Rows("63:69").EntireRow.Hidden = True
      Rows("21:62").EntireRow.Hidden = False
       End If
       
        If Target.Value = 8 Then
      Rows("21:69").EntireRow.Hidden = False
       End If
       
       End If
       
       Application.EnableEvents = True
End Sub

[DonkeyOte]

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xlCalc As XlCalculation, lngRow As Long
On Error GoTo ExitPoint
If Intersect(Target, Range("I3")) Is Nothing Then Exit Sub
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
Rows("14:69").Hidden = False
Select Case Range("I3").Value
    Case 0 To 7
        lngRow = 14 + 7 * Range("I3")
        Rows(lngRow & ":69").Hidden = True
End Select
ExitPoint:
With Application
    .Calculation = xlCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

To insert the above right click on tab against which the code is to be applied - select View Code and insert into resulting window thereafter ensure Macros are enabled.

I also need this macro to work to work it the worksheet is coppied into the same workboob multiple times.

if you right click on tab and select to Copy you should find the code will fire on the replica sheet also (separate events - ie the copied sheet does not affect original and vice-versa)

[MGK086]

That worked great Thanks!!!

[annie_an]

Hi there,

I found this thread extremely useful as I needed to hide rows depending on a drop-down cell number.

But what if you have more than one drop-downs on the same sheet and consequently more than one set of rows that need to be hidden? Is it possible to adjust the VBA code to accommodate this?

Thank you,
annie_an

[Pichingualas]

Hi. Welcome to the forum

Yes it is. You should make your own thread and post a sample file for people to see, asking in someone else's thread is against forum rules.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

Also:

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.