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

J

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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
 
Upvote 0
JLGWhiz said:
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
Click to expand...
Thanks for the response and attempt but this is throwing out and error message:
Compile error: Ambiguous name detected: Worksheet_Change
 
Upvote 0
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.
 
Upvote 0
JLGWhiz said:
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.
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA Code for hiding columns based on multiple user inputs
2
Replies
14
Views
730
kevin9999
K
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
210
rgree05
R
N
  • Question
Hiding rows based on cell value
Replies
2
Views
59
smozgur
smozgur
H
  • Question
Hide Columns VBA - refusing to hide random columns
Replies
2
Views
250
hmltnangel
H
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
13
Views
195
mumps
mumps

Forum statistics

Threads
1,214,431
Messages
6,119,458
Members
448,899
Latest member
maplemeadows

Share this page

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