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

Steve1970

New Member
Joined
Feb 6, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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

1612650769399.png


1612650835809.png
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about this, pasted into the sheet module
VBA Code:
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

New Member
Joined
Feb 6, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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

VBA Code:
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

New Member
Joined
Feb 6, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS

ADVERTISEMENT

How about this, pasted into the sheet module
VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
... 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 .

VBA Code:
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
 
Solution

Steve1970

New Member
Joined
Feb 6, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
Change target cells addresses and rows numbers as needed .

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,821
Messages
5,638,532
Members
417,032
Latest member
Qasdrf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top