Hide rows based on muti cell drop down list

[prat33]

Hi guys, need help urgently on this.

Have Cell B39, which has Yes/No drop down option.
If no, then I want to hide rows 40-66.
If yes, then I want it to show row 40, hide rows 41-66.

Then, Cell B40 has Yes/No drop down option.
If no, then I want to hide rows 41-66.
If yes, then I want it to show row 41, hide rows 42-66.

Then, Cell B41 has 1,2,3,....,25 drop down options.
If 1, then I want it to show row 42, hide rows 43-66.
If 2, then I want it to show row 42 and 43, hide rows 44-66.
If 3, then I want it to show row 42,43, and 44, hide rows 45-66.
and so forth..

Can someone please help me? I don't have experience with Macros/VBA.

[Winon]

Hello prat33,

Welcome to the Forum with your first Post!

Please could you be so kind as to attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards.

[mgs73]

Try this (insert code in relevant Worksheet Module)

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Select Case Target.Address
    
  Case "$B$39"
    If Target.Value = "No" Then
      Me.Range(Rows(40), Rows(66)).Hidden = True
    ElseIf Target.Value = "Yes" Then
      Me.Rows(40).Hidden = False
      Me.Range(Rows(41), Rows(66)).Hidden = True
    End If
  
  Case "$B$40"
    If Target.Value = "No" Then
      Me.Range(Rows(41), Rows(66)).Hidden = True
    ElseIf Target.Value = "Yes" Then
      Me.Rows(41).Hidden = False
      Me.Range(Rows(42), Rows(66)).Hidden = True
    End If
  
  Case "$B$41"
    Me.Range(Rows(42), Rows(41 + Target.Value)).Hidden = False
    Me.Range(Rows(42 + Target.Value), Rows(66)).Hidden = True
  End Select
End Sub

[prat33]

Ok hopefully the attachment has come up..

@mgs73, that didn't work.

[mgs73]

I just tried it on your file, and it worked ok for me (see attached).

[Winon]

Hi prat33,

Thank you for your response, however your sample workbook contains no Code at all, and the Before and After Sheets shows nothing at all.

Make sure there is just enough data to demonstrate your need.

Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Thank you.

[prat33]

Hi, sorry because I don't know what codes to add, I do not have any experience with VBA etc. The code posted above didn't work too.

Sorry as I mentioned in my initial post, what I am trying to achieve is:

Have Cell B39, which has Yes/No drop down option.
If no, then I want to hide rows 40-66.
If yes, then I want it to show row 40, hide rows 41-66.

Then, Cell B40 has Yes/No drop down option.
If no, then I want to hide rows 41-66.
If yes, then I want it to show row 41, hide rows 42-66.

Then, Cell B41 has 1,2,3,....,25 drop down options.
If 1, then I want it to show row 42, hide rows 43-66.
If 2, then I want it to show row 42 and 43, hide rows 44-66.
If 3, then I want it to show row 42,43, and 44, hide rows 45-66.
and so forth..

[prat33]

@mgs73, just tried again and it worked! thank you soo much.