Macro - Find a cell and hide columns

[CJG19]

Good Morning,

I am new to Macros and I am having an issue with one I am trying to write my second at the moment.

I have months at the top of my columns from Jan 2021 to Dec 2026. I want to have a button so I can hide all previous months and all future months, just showing the current month. I thought maybe some sort of marker above the current month, but I am not sure that would work. My other idea is base it on today's date?

Any help would be appreciated.

Many Thanks!

CJG19

 

[Joe4]

Welcome to the Board!

If your title row with months is actually entered as valid dates, this VBA code should do what you want:

Sub HideColumns()

    Dim rng As Range
    Dim cell As Range
    
'   Designate which cells to check (all in one row)
    Set rng = Range("A1:L1")
    
'   First, unhide all columns
    rng.EntireColumn.Hidden = False
    
'   Loop through cells and hide columns
    For Each cell In rng
'       Check to see if current month/year
        If Format(cell, "mmyy") <> Format(Date, "mmyy") Then
'           Hide if not equal
            cell.EntireColumn.Hidden = True
        End If
    Next cell
    
End Sub

Just change this line to match the cells containing your date headers:
Set rng = Range("A1:L1")

 

[CJG19]

Thank you Joe4, it works perfectly for what I need!

 

[Joe4]

You are welcome.
Glad I was able to help!