Hide columns based on cell value

cafeannalisa

New Member
Joined
Apr 29, 2017
Messages
9
Office Version
  1. 2016
Platform
  1. MacOS
I have much the same question as but can't get the answers to work
Columns J1-N1 have headers 1 2 3 4 5
I want to hide columns based on S1, which takes values 1-5
If S1 = 1 Hide columns headed 2-5 i.e. K:N
If S1 = 2 Hide columns headed 3-5 i.e.L:N
If S1 = 3 Hide columns headed 4-5 i.e.M:N
If S1 = 4 Hide column headed 5 i.e. N
[If S1 = 5 No hiding]
I have tried a version of alansidman's code on the earlier thread without success.

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range(“S1")
If Target = 1 Then
columns(“K:N”).Hidden = True
ElseIf Target = 2 Then
columns("K:N").Hidden = False
columns(“L:N").Hidden = True
ElseIf Target = 3 Then
columns("K:N").Hidden = False
columns(“M:N").Hidden = True
ElseIf Target = 4 Then
columns("K:N").Hidden = False
columns(“N”).Hidden = True
End If
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
Ok, how is S1 being changed?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
Ok, in that case is the code in the relevant sheet module, or in a standard module?
 

cafeannalisa

New Member
Joined
Apr 29, 2017
Messages
9
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

Ok, in that case is the code in the relevant sheet module, or in a standard module?
i think it is a standard module - first time i have done this. Its in a 'Modules' folder at top level in the Project Sidebar
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
Ok, you need to put the code in the relevant sheet module, in Xl right click the tab of the sheet you want the code to work on & select view code, then paste the code into the window that opens up.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
You could also use this code, which does the same thing
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "S1" Then
      Range("K:N").EntireColumn.Hidden = False
      Select Case Target
         Case 1 To 4
            Cells(1, Target + 10).Resize(, 5 - Target.Value).EntireColumn.Hidden = True
      End Select
   End If
End Sub
 

cafeannalisa

New Member
Joined
Apr 29, 2017
Messages
9
Office Version
  1. 2016
Platform
  1. MacOS
Ok, you need to put the code in the relevant sheet module, in Xl right click the tab of the sheet you want the code to work on & select view code, then paste the code into the window that opens up.
Yes thanks so much for a New Year breakthrough, I learned something crucial obviously!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,087
Messages
5,628,593
Members
416,326
Latest member
NinaChristal

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