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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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

Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
367
ysfuyar
Y
V
  • Solved
Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
Replies
2
Views
131
vanessavalentino83
V
D
  • Question Question
Hide Rows Based on Another Sheets Values
Replies
9
Views
322
Joe4
Joe4
A
  • Solved
Hide Column with multiple command buttons
2
Replies
10
Views
361
arisomar
A
M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
562
Minx_Minxy
M

Forum statistics

Threads
1,224,847
Messages
6,181,352
Members
453,033
Latest member
lapmangviettel

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