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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Maybe you are using the wrong event. Try this and see if does what you want.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2")) Is Nothing Then
    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 If
End Sub
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Maybe you are using the wrong event. Try this and see if does what you want.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2")) Is Nothing Then
    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 If
End Sub
Thanks for the response and attempt but this is throwing out and error message:
Compile error: Ambiguous name detected: Worksheet_Change
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
That simply means that you already have a change event macro in that worksheet code module and you can only have one procedure for that event. But it is possible to append the new procedure to the old one so that they both work when the conditions are met. But I don't think you will resolve the issue in this thread if you continue to use the Worksheet_SelectionChange event because the procedure runs as soon as you move the mouse pointer to a different cell, which means that if you select cell L2 the procedure runs before you can change the value in the cell, and changing the values does no good unless you move the mouse cursor again. If you want to post the other Worksheet_Change procedure, I will see if they can be easily combined so that you get the desired results in either case.
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

That simply means that you already have a change event macro in that worksheet code module and you can only have one procedure for that event. But it is possible to append the new procedure to the old one so that they both work when the conditions are met. But I don't think you will resolve the issue in this thread if you continue to use the Worksheet_SelectionChange event because the procedure runs as soon as you move the mouse pointer to a different cell, which means that if you select cell L2 the procedure runs before you can change the value in the cell, and changing the values does no good unless you move the mouse cursor again. If you want to post the other Worksheet_Change procedure, I will see if they can be easily combined so that you get the desired results in either case.
Thank you for the explanation. I do have a code that is hiding columns based on a cell. here is the complete VBA, including the code you provided:


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim ws As Worksheet
   
    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
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2")) Is Nothing Then
    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 If
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I am not sure how you use the first one that hides and unhides sheets in a worksheet change event. What I would suggest is that you change that one to a Worksheet_activate event. Then when you open that sheet it will check the sheet names and hide/unhide the sheets per the code. here is how the macros would then look.

VBA Code:
Private Sub Worksheet_Activate()
    Dim ws As Worksheet
    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
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2")) Is Nothing Then
    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 If
End Sub

You can try it and see if it works for you, if not then we can explore other possibilities.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

If changing the old code to worksheet_activate is not satisfactory, then this would merge the two into a single worksheet_change procedure, controlled by the If...Then statements.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
    Dim ws As Worksheet
    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
    If Not Intersect(Target, Range("L2")) Is Nothing Then
        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 If
End Sub
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Thank you once again. The worksheet_activate code was not effective at hiding sheets based on the content of M2. I tried putting in the single code with If...Then. This was at least successful at hiding the sheets, but not the columns. For some reason the columns are not becoming hidden
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Don't understand why it is not working for you, it works in my test setup. The code is in the same worksheet where the Range("L2") value will be evaluated. If you have it in a different sheet code module, then the columns would not hide. Note that the code to hide the sheets is based on value in "M2" and columns are based on values in "L2".
 

jwing3

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
I am not sure either the Columns are based on L2 and the Sheets on M2
from looking at it the code should work, but it is only Hiding the sheets and not the columns
 

Watch MrExcel Video

Forum statistics

Threads
1,126,908
Messages
5,621,588
Members
415,844
Latest member
nigeywigey

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