VBA Code for hiding columns based on multiple user inputs

[Curdood]

Hi All,

New to VBA and looking for some help. I want to hide columns based on multiple user selections.

For this example, if a user wants to filter by Fiscal Quarter, Q1, Q2 (Row 3 values from column F onwards).... and give them the option to filter by Fiscal Year, FY24, FY23.... (Row 4 values from Column F onwards).
I've figured out how to hide by one input, in this case Quarter, but looking for some help on how to include the second.

B3 = Q1, Q2, Q3, Q4, All
B4 = FY24, FY23, FY22, FY21, All

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant, y As String
If Target.Address = "$B$3" Then
y = Target.Value
With Range("F3:FF3")
Application.ScreenUpdating = False
.EntireColumn.Hidden = (y <> "All")
If y <> "All" Then
For Each x In .Cells
If x = y Then x.EntireColumn.Hidden = False
Next
End If
Application.ScreenUpdating = True
End With
End If
End Sub

 

[kevin9999]

Your image doesn't match your description, and changes structure from your original post. The financial years and quarters have swapped positions, also your data validation cells appear to be in the range B4:B6 and not B3:B5, with the FY cells in row 1. I'll write the code according to your image, not your description, unless I hear from you first. It seems to make more sense (given the new structure) to put the financial year option at the top of the three data validations.

 

[Curdood]

Your image doesn't match your description, and changes structure from your original post. The financial years and quarters have swapped positions, also your data validation cells appear to be in the range B4:B6 and not B3:B5, with the FY cells in row 1. I'll write the code according to your image, not your description, unless I hear from you first. It seems to make more sense (given the new structure) to put the financial year option at the top of the three data validations.

Thanks. You are correct, I quickly drew that up as an example and it is not presented well. But if you are to work off the image, columns F onwards make sense, and I need to make FY the first input cell, which can be at B3 or B4 (I Will adjust). Thanks!

 

[kevin9999]

Please try the following code, which is based on this layout of your data:

select case.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1					FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22	FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22	FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22
2					Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3					Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast
4	All
5	All
6	All
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B4	List	FY22,FY23,FY24,All
B5	List	Q1,Q2,Q3,Q4,All
B6	List	Actual,Budget,Reforecast,All

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B4:B6"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim x As String, y As String, c As Range, z As String
        x = Range("B4"): y = Range("B5"): z = Range("B6")
        
        Range("F:FF").EntireColumn.Hidden = False
        Select Case x
            Case "All"
                Select Case y
                    Case "All"
                        Select Case z
                           Case "All"
                        Case Else
                            For Each c In Range("F3:FF3")
                                If c <> z Then c.EntireColumn.Hidden = True
                            Next c
                        End Select
                    Case Else
                        For Each c In Range("F2:FF2")
                            If c <> y Then c.EntireColumn.Hidden = True
                        Next c
                End Select
            Case Else
                For Each c In Range("F1:FF1")
                    If c <> x Then c.EntireColumn.Hidden = True
                    If c.Offset(1) <> y And y <> "All" Then c.EntireColumn.Hidden = True
                    If c.Offset(2) <> z And z <> "All" Then c.EntireColumn.Hidden = True
                Next c
        End Select
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[Curdood]

Please try the following code, which is based on this layout of your data:

select case.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1					FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22	FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22	FY24	FY24	FY24	FY24	FY23	FY23	FY23	FY23	FY22	FY22	FY22	FY22
2					Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3					Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast	Reforecast
4	All
5	All
6	All
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B4	List	FY22,FY23,FY24,All
B5	List	Q1,Q2,Q3,Q4,All
B6	List	Actual,Budget,Reforecast,All

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B4:B6"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim x As String, y As String, c As Range, z As String
        x = Range("B4"): y = Range("B5"): z = Range("B6")
       
        Range("F:FF").EntireColumn.Hidden = False
        Select Case x
            Case "All"
                Select Case y
                    Case "All"
                        Select Case z
                           Case "All"
                        Case Else
                            For Each c In Range("F3:FF3")
                                If c <> z Then c.EntireColumn.Hidden = True
                            Next c
                        End Select
                    Case Else
                        For Each c In Range("F2:FF2")
                            If c <> y Then c.EntireColumn.Hidden = True
                        Next c
                End Select
            Case Else
                For Each c In Range("F1:FF1")
                    If c <> x Then c.EntireColumn.Hidden = True
                    If c.Offset(1) <> y And y <> "All" Then c.EntireColumn.Hidden = True
                    If c.Offset(2) <> z And z <> "All" Then c.EntireColumn.Hidden = True
                Next c
        End Select
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Thank you, this is exactly what I needed and works great!

Much appreciated and thanks for the quick turnaround.

 

[kevin9999]

Glad it worked & thanks for letting me know