Hide/unhide Rows, based on single cell drop down options

[Steve1970]

I'm new to VBA code. I'm working with a worksheet called 'Working Example - Org Context'. The client will choose in cell G15, either "N/A" or a "Blank" option from the drop-down list. If "N/A" is chosen rows "17-25" will be hidden in the worksheet. However, if the "Blank" option is chosen, then the rows "17-25" will be visible/unhidden. This is the first time I've used VBA, so if anyone is able to provide a code and the process for adding it, it would be greatly appreciated. I've added two images to demo what I'm trying to achieve. Thanking you in advance.

Best, Steve

 

[Osvaldo Palmeiro]

Hi. Try the code below please.

A copy of it should go into the worksheet 'Working Example - Org Context' module, this way:
1. Copy the code from the board
2. Right click on that sheet tab and choose "View Code"
3. Paste the code into the blank window that will open
4. done! 'Alt + Q' to return to the worksheet and test

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$G$15" Then Rows("17:25").Hidden = [G15] = "#N/A"
End Sub

 

[Michael M]

How about this, pasted into the sheet module

Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Target.Address(False, False) = "G15" Then Exit Sub
If Range("G15").Value = "N/A" Then
Rows("17:25").EntireRow.Hidden = True
Else
Rows("17:25").EntireRow.Hidden = False
End If
End Sub

 

[Steve1970]

Hi. Try the code below please.

A copy of it should go into the worksheet 'Working Example - Org Context' module, this way:
1. Copy the code from the board
2. Right click on that sheet tab and choose "View Code"
3. Paste the code into the blank window that will open
4. done! 'Alt + Q' to return to the worksheet and test

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$15" Then Rows("17:25").Hidden = [G15] = "#N/A"
End Sub

Thank you Osvaldo for the instruction and code. I tried the code you wrote, but this didn't work, unfortunately. But, I appreciate you taking the time to send it through. I viewed it and the code was exactly as you'd written it, but it didn't provide a solution. KR, Steve

 

[Steve1970]

How about this, pasted into the sheet module

Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Target.Address(False, False) = "G15" Then Exit Sub
If Range("G15").Value = "N/A" Then
Rows("17:25").EntireRow.Hidden = True
Else
Rows("17:25").EntireRow.Hidden = False
End If
End Sub

Evening Michael. Thank you for taking the time to write and send through this code. I inserted this into the worksheet and tested and it hid/unhid the rows as I wanted, which is perfect. I do have one further question. I have several questions within the same worksheet where I need to apply the same logic, where the cell value "N/A" will be used in the same way, but the rows that will be hidden will obviously be different. Do, I need to adapt the code you sent me, or do I somehow write the same code, but change the cell/row references? If you were able to advise that would be great.

 

[Osvaldo Palmeiro]

Hi, Steve.
Sorry, my mistake.

If Target.Address = "$G$15" Then Rows("17:25").Hidden = [G15] = "#N/A" ~~~> this should be "N/A"

 

[Osvaldo Palmeiro]

... same logic, where the cell value "N/A" will be used in the same way, but the rows that will be hidden will obviously be different.

Change target cells addresses and rows numbers as needed .

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim str As String
  Select Case Target.Address
   Case "$G$15": str = "17:25"
   Case "$H$15": str = "27:35"
   Case "$K$15": str = "37:45"
  End Select
  If str <> "" Then Rows(str).Hidden = Target.Value = "N/A"
End Sub

 

[Steve1970]

Change target cells addresses and rows numbers as needed .

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
  Select Case Target.Address
   Case "$G$15": str = "17:25"
   Case "$H$15": str = "27:35"
   Case "$K$15": str = "37:45"
  End Select
  If str <> "" Then Rows(str).Hidden = Target.Value = "N/A"
End Sub

Osvaldo, that's perfect. Works just as I want it too. Really appreciate you taking the time to update the following question. Thank you.