Macro to hide all columns that are before current month

[tonywatsonhelp]

Hi Everyone,

I have a ist of columns with dates in row 20 full range is "AB20:DXX20"

All i want to do is have a macro that when i run it Hide all dates before todays month so right now its 11 may so everying before 1st may

please help if you can

Thanks

Tony

 

[JoeMo]

Are the dates numbers or text?
What's the time increment between adjacent cells?

 

[JLGWhiz]

If the dates are consecutive then this might work.

Sub t2()
Dim fn As Range, dt As Date
    dt = Application.EoMonth(Date, -1)
    With ActiveSheet
        .Range("AB:DXX").EntireColumn.Hidden = False
        MsgBox dt
        Set fn = .Range("AB20", .Cells(20, Columns.Count).End(xlToLeft)).Find(dt, , xlValues)
            If Not fn Is Nothing Then
                .Range("AB20", fn).EntireColumn.Hidden = True
            End If
    End With
End Sub

Otherwise, you might need something like this

Sub t()
Dim c As Range
    With ActiveSheet
        .Range("AB:DXX").EntireColumn.Hidden = False
        For Each c In .Range("AB20", .Cells(20, Columns.Count).End(xlToLeft))
            If c.Value < Date And Month(c.Value) <> Month(Date) Then
                c.EntireColumn.Hidden = True
            End If
        Next
    End With
End Sub

 

[JoeMo]

Assuming dates are numbers in ascending order and increment is one day between adjacent cells, here's another non-looping solution:

Sub HidePriorMonths()
Dim R As Range, Adr, LastCol As Long
Set R = Range("AB20:DXX20")
Application.ScreenUpdating = False
R.Columns.Hidden = False
Adr = [MATCH(TODAY(),AB20:DXX20,0)]
LastCol = R.Columns(Adr - Day(Date)).Column
Range(R.Cells(1, 1), Cells(R.Row, LastCol)).EntireColumn.Hidden = True
Application.ScreenUpdating = True
End Sub