Hide Columns Based on Other Sheet Cell Value

VrRotate

New Member
Joined
Mar 11, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello,

I've used several examples and can't get this to work.

Condition:
Drop down on Sheet1 cell B7 Yes or No

If No hide Sheet2 Columns CN - EL
If Yes show Sheet2 Columns CN - EL

Want to work in real time as user selects Yes or No, if that's possible?

Thank you in advance for all your help!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B7.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Yes"
            Sheets("Sheet2").Columns("EL:CN").Hidden = False
        Case "No"
            Sheets("Sheet2").Columns("EL:CN").Hidden = True
    End Select
End Sub
 
Last edited:
Upvote 0
Solution
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B7" Then
      Sheets("Sheet2").Columns("CN:EL").Hidden = Target.Value = "No"
   End If
End Sub
This needs to go in the sheet module with the drop-down
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B7.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Yes"
            Sheets("Sheet2").Columns("EL:CN").Hidden = False
        Case "No"
            Sheets("Sheet2").Columns("EL:CN").Hidden = True
    End Select
End Sub

This worked perfect!! Thank you!!
 
Upvote 0
Do you want the "N" to be entered only when the columns are hidden or when they are visible as well? Please clarify.
 
Upvote 0
Which cells? Surely you do not mean every cell in the entire columns

Yes, it would be a N in every column - its a spreadsheet that gets uploaded to update data so it can't be blank.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top