Macro to hide past months?

[tonywatsonhelp]

Hi Everyone,

I have a calendar or list of dates in sheet Cal row 10 range M10:AYX10

each cell has a date in it from M10 = 01/Jan/2018 on

now what id like to do is hide the Months that have past

So when the workbook opens it takes todays date, looks along Row 10 finds the date and Hides all the column before the first of the current month.

any ideas how to do this?

Thanks

Tony

 

[wideboydixon]

Assuming it's on the first sheet, try this in the Workbook_Open() handler:

Private Sub Workbook_Open()

Const firstDateCell = "M10"

Dim firstDate As Date
Dim lastDate As Date

firstDate = Sheets(1).Range(firstDateCell).Value
lastDate = Application.WorksheetFunction.EoMonth(Date, -1)

If lastDate >= firstDate Then
    Sheets(1).Range(firstDateCell).Resize(1, lastDate - firstDate + 1).EntireColumn.Hidden = True
End If

End Sub

WBD