Hide and Unhide columns based on multiple criteria

[SamoZain]

Hi Guys, I have similar question and I was hoping someone can help me with it as I am trying to develop my VBA capabilities.

I have an excel file that will hide and unhide based on multiple values which mainly year and month criteria, meaning:
if it is 2016 chosen then all the columns of 2016 will be unhiden. but if within 2016 I want only column of Feb then I will choose only Feb.

my question is how can I make nested choices in a way that if I choose Feb (After choosing 2016) it will show me all the Feb which has all the years in???

Best

 

[louisH]

Hi,

Are your columns headers actual Date format ?

If so this could put you on the track :

Sub SelectYearColumns()
    
    Dim c As Range
    Dim yr As Double
    yr = 2016

    For Each c In Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
        If Year(c.Value) = yr Then
            c.EntireColumn.Hidden = False
        Else
            c.EntireColumn.Hidden = True
        End If
    
    Next c
    
End Sub


Sub SelectMonthColumns()
    
    Dim c As Range
    Dim yr As Double
    yr = 2016
    Dim mnth As String
    mnth = "february"
    For Each c In Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
        If Year(c.Value) = yr And Format(c.Value, "mmmm") = mnth Then
            c.EntireColumn.Hidden = False
        Else
            c.EntireColumn.Hidden = True
        End If
    
    Next c
    
End Sub

 

[SamoZain]

Hi,

Are your columns headers actual Date format ?

If so this could put you on the track :

Sub SelectYearColumns()
    
    Dim c As Range
    Dim yr As Double
    yr = 2016

    For Each c In Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
        If Year(c.Value) = yr Then
            c.EntireColumn.Hidden = False
        Else
            c.EntireColumn.Hidden = True
        End If
    
    Next c
    
End Sub


Sub SelectMonthColumns()
    
    Dim c As Range
    Dim yr As Double
    yr = 2016
    Dim mnth As String
    mnth = "february"
    For Each c In Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
        If Year(c.Value) = yr And Format(c.Value, "mmmm") = mnth Then
            c.EntireColumn.Hidden = False
        Else
            c.EntireColumn.Hidden = True
        End If
    
    Next c
    
End Sub

Thanks a lot for the code louisH. my bad I didn't elaborate more.
what I did is that I used FormControl botton to have years (2016, 2017, 2018..etc) and another one for the Months (Feb, March, April...etc)
and when the user click on botton of 2016 so all the months of 2016 are unhiden. and if want only specific month then get to click on speicifc month. any help is really appreciated?