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]

Please try the following on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B3:B4"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim x As String, y As String, c As Range
        x = Range("B3"): y = Range("B4")
        
        Range("F:FF").EntireColumn.Hidden = False
        Select Case x
            Case "All"
                Select Case y
                    Case "All"
                    Case Else
                    For Each c In Range("F4:FF4")
                        If c <> y Then c.EntireColumn.Hidden = True
                    Next c
                End Select
            Case Else
                For Each c In Range("F3:FF3")
                    If c <> x Then c.EntireColumn.Hidden = True
                    If c.Offset(1) <> y And y <> "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 on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B3:B4"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim x As String, y As String, c As Range
        x = Range("B3"): y = Range("B4")
       
        Range("F:FF").EntireColumn.Hidden = False
        Select Case x
            Case "All"
                Select Case y
                    Case "All"
                    Case Else
                    For Each c In Range("F4:FF4")
                        If c <> y Then c.EntireColumn.Hidden = True
                    Next c
                End Select
            Case Else
                For Each c In Range("F3:FF3")
                    If c <> x Then c.EntireColumn.Hidden = True
                    If c.Offset(1) <> y And y <> "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 works perfectly.
Is it possible to amend for a third, call it Z = Range ("B5")? I suppose there could be endless iterations, but am struggling to set up.

 

[kevin9999]

Thank you this works perfectly.

Glad it worked out for you, and thanks for the feedback

Is it possible to amend for a third, call it Z = Range ("B5")?

It should be possible - could you provide more details: criteria, range/row to assess?

 

[Curdood]

Glad it worked out for you, and thanks for the feedback

It should be possible - could you provide more details: criteria, range/row to assess?

Thanks.

B5 = Actual, Budget, Reforecast, All...
It would impact F5:FF5.

 

[kevin9999]

Thanks.

B5 = Actual, Budget, Reforecast, All...
It would impact F5:FF5.

Could you provide a sample of how they would look across the sheet? I'm trying to visualise how they would relate to the Qs and FYs.

 

[Curdood]

The goal would be to input in the yellow highlighted cells, B3:B5 (and potentially more further on). Which would make it easier to hide cells F onwards based on those 3 fields.

 

[Curdood]

Could you provide a sample of how they would look across the sheet? I'm trying to visualise how they would relate to the Qs and FYs.

The goal would be to input in the yellow highlighted cells, B3:B5 (and potentially more further on). Which would make it easier to hide cells F onwards based on those 3 fields.

 

[kevin9999]

OK, thanks for that.

 

[kevin9999]

Leave it with me.