Hide Multiple Columns based on a cell value - "hide these or hide these or these"

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
I am working tying to have the ability to hide columns based on a cell value where
Cell = L2
If the value in L2 = "Q1" then columns AD:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q2" then columns T:AD & AO:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q3" then columns T:AO & AZ:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q4" then columns T:AZ should be hidden, all other columns should be visible

The following is what I have, but obviously I am missing something because it is not working.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("L2").Value = "Q1" Then
Columns("AD:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q2" Then
Columns("T:AD,AO:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q3" Then
Columns("T:AO,AZ:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q4" Then
Columns("T:AZ").EntireColumn.Hidden = True
Else
Columns("T:BJ").EntireColumn.Hidden = False
End If
End Sub

Any Help is appreciated - J
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Maybe if I explain what the code does it will clear things up.

1. If you make a change in cell M2, it will hide the sheets. It will not hide the columns because cell L2 was not the target.
2. If you make a change in cell L2, it will hide the columns according to the value entered in cell L2. It will also hide the sheets if the Brandon criteria is met.
3. The sheets criteria is not dependent on the target value, but the columns criteria is dependent on cell L2 being the target.

So if you change cell L2 and the value in that cell is Q1, Q2, Q3 or Q4 then the columns should hide accordingly. Otherwise, the columns will not hide.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Continued:
This is exactly what the two codes were doing before as separate procedures. The only need for combining them into one procedure was to satisfy the limitations of the Excel application which will not allow two of the same event procedures in one code module, In a regular code module you can just change the name of one procedure, but in the worksheet code module the title of the event procedure is similar to an Excel function. The event procedure title line must be exactly as the VBA application expects it to be or it will not execute as intended. So in writing the code, both the Excel limitations and the VBA limitations have to be adhered to or the code fails.
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
JLG I have got it working now with only one issue
The input in cell L2 is equal of a formula. If I let the formula run, the columns do not hide.
however, if I manually change the content of L2 then the macro runs the way it should and the columns hide.

Would having the contents of L2 be based off of a formula make a difference?
if so what can I do to correct?

And thank you again for all your attention and help
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If L2 is chaged by the result of a formula then you cannot combine the two procedures under a Worksheet_Change event because Excel does not see calculated results as changes. You would need to go back to separate procedures. as shown below.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim ws As Worksheet
    Application.EnableEvents = False
    For Each ws In Worksheets
        If ws.Name <> "Brandon" And ws.Name <> Worksheets("Brandon").Range("M2").Value Then
            ws.Visible = False
        End If
        If ws.Name = Worksheets("Brandon").Range("M2").Value Then
            ws.Visible = True
        End If
    Next ws
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    ActiveSheet.Columns.Hidden = False
        If Range("L2").Value = "Q1" Then
            Range("AD:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q2" Then
            Range("T:AD,AO:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q3" Then
            Range("T:AO,AZ:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q4" Then
            Range("T:AZ").EntireColumn.Hidden = True
        Else
            Range("T:BJ").EntireColumn.Hidden = False
        End If
End Sub
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
That is perfect - it all works!!
Thank you so much for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,126,938
Messages
5,621,715
Members
415,853
Latest member
Newlife72

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