Sep 03 2021 06:41 AM
Hello! I have an excel document that has details on types of plans and then whether the plan is offered in a specific county. Each plan is a column and each County is in it's own row with "Y" or "N" under each county, to show whether the plan is available in that county. I would like to set it up so that if I select a County in a drop-down list, it hides the columns that have the "N" value for that county's row. Is this possible? I've attached a screenshot of the document, so you have a visual of what I'm trying to do.
Please note I am using Excel on a Mac. Thanks very much for any help you can provide!
Sep 03 2021 07:07 AM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
I made some assumptions based on your screenshot:
If they are incorrect you'll have to modify the code.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim r As Long
If Not Intersect(Range("G2"), Target) Is Nothing Then
Application.ScreenUpdating = False
Range("B1:E1").EntireColumn.Hidden = False
If Range("G2").Value <> "" Then
r = Range("A:A").Find(What:=Range("G2").Value, LookAt:=xlWhole).Row
For c = 2 To 5 ' columns B to E
If Cells(r, c).Value = "N" Then
Cells(r, c).EntireColumn.Hidden = True
End If
Next c
End If
Application.ScreenUpdating = True
End If
End Sub
Sep 10 2021 06:09 AM
Sep 10 2021 06:24 AM
You're welcome! Glad to have been able to help.
Sep 03 2021 07:07 AM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
I made some assumptions based on your screenshot:
If they are incorrect you'll have to modify the code.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
Dim r As Long
If Not Intersect(Range("G2"), Target) Is Nothing Then
Application.ScreenUpdating = False
Range("B1:E1").EntireColumn.Hidden = False
If Range("G2").Value <> "" Then
r = Range("A:A").Find(What:=Range("G2").Value, LookAt:=xlWhole).Row
For c = 2 To 5 ' columns B to E
If Cells(r, c).Value = "N" Then
Cells(r, c).EntireColumn.Hidden = True
End If
Next c
End If
Application.ScreenUpdating = True
End If
End Sub