Hide Columns Older than Today

Try this:

Sub HideCols()
    Dim cell As Range
    Dim lcol As Integer
    lcol = ActiveSheet.UsedRange.Columns.Count
    For Each cell In Range(Cells(1, 1), Cells(1, lcol))
        If cell.Value < Date Then cell.EntireColumn.Hidden = True
    Next
End Sub

Hi,

This will start from column B

Sub HideCols()
    Dim cell As Range
    Dim lcol As Integer
    lcol = ActiveSheet.UsedRange.Columns.Count
    For Each cell In Range(Cells(1, 2), Cells(1, lcol))
        If cell.Value < Date Then cell.EntireColumn.Hidden = True
    Next
End Sub

To start it when you open the workbook under ThisWorkbook use this:

Private Sub Workbook_Open()
    Call HideCols
End Sub

Why in a seperate module?

Private Sub Workbook_Open()
    Dim i as long
    With Sheets("Sheet1") 'change to need
        For i = 2 To 14
            .Columns(i).Hidden = False
            If .Cells(1, i) < Date Then .Columns(i).Hidden = True
        Next
    End With
End Sub

@ jindon

Also works nice but...

Private Sub Workbook_Open()
    Dim r As Range
    For Each r In Sheets("Blad1").Cells(1).CurrentRegion.Offset(, 1).Rows(1).Cells
        r.EntireColumn.Hidden = (r <> "") * (r < Date)
    Next
End Sub

You must put the code in the ThisWorkbook module, NOT a sheet module or a standard module

This should do it.

    Dim i As Long
    Application.ScreenUpdating = False
    With Activesheet 'change to need
        For i = 2 To .UsedRange.Columns.Count
            .Columns(i).Hidden = False
            If .Cells(1, i) < Date Or .Cells(1, i) > Date + 9 Then .Columns(i).Hidden = True
        Next
    End With
    Application.ScreenUpdating = True

That worked great! See attached screenshot of both codes used.

Now I need to do the same for data in weekly buckets. Essentially we have the same report format but data in weekly buckets instead of days. I need to hide columns with weekly buckets older than this week, but show the next 10 (let's say to be consistent) weeks of data. How do I change from using days to weeks?

Note: Refer to the first screenshot, not the one where I've attempted weeks. It won't let me delete the old incorrect image.

sample_vba_5.PNG

Please post example file with what your data looks like in Row1.

If .Cells(1, i) < Date Or .Cells(1, i) > Date + 70 Then .Columns(i).Hidden = True

If we use the above then only columns M to V would be visible. Is that what your after ?

That complicates things a little because '.Cells(1, i) < Date' won't work anymore.
In case of today's date (or for that matter all days of this week) you would like to see the column of 10/10/2016 also, yes?
If that's the case we'll have to calculate which week today's date is in, calculate first day of that week (in case of today's date all through Sunday 16/10 would be 10/10) to include that column also.
Does this sum it up a little ?

Paste below in your module and see if this gives you expected result.
Don't forget to remove formula in A1 or column will also be hidden.

Public Function ISOweeknum(ByVal v_Date As Date) As Integer
    ISOweeknum = DatePart("ww", v_Date - Weekday(v_Date, 2) + 4, 2, 2)
End Function

Public Function ISOday(v_year As Integer, v_week As Integer, v_day As Integer) As Long
    ISOday = 7 * (v_week - 1) + DateSerial(v_year, 1, 4) - Weekday(DateSerial(v_year, 1, 4), 2) + v_day
End Function

Sub HideCols()
    Dim i As Long, stDate As Date
    stDate = ISOday(Year(Date), ISOweeknum(Date), 1)
    Application.ScreenUpdating = False
    With ActiveSheet 'change to need
        For i = 2 To .UsedRange.Columns.Count
            .Columns(i).Hidden = False
            If .Cells(1, i) < stDate Or .Cells(1, i) > stDate + 65 Then .Columns(i).Hidden = True
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Glad to help.

Back to the daily one...Code that works is posted below...

What happens if there is an additional set of data in Column B (let's say names or products) and the daily columns start in Column C. What do I change to make it start with the 3rd column now, instead of 2nd? I've figured out that "For i=2" goes to "For i=3". What else?

  Dim i As Long
    Application.ScreenUpdating = False
    With Activesheet 'change to need
        For i = 2 To .UsedRange.Columns.Count
            .Columns(i).Hidden = False
            If .Cells(1, i) < Date Or .Cells(1, i) > Date + 9 Then .Columns(i).Hidden = True
        Next
    End With
    Application.ScreenUpdating = True

Short answer. Nothing else.

  Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet 'change to need
        For i = 3 To .UsedRange.Columns.Count
            .Columns(i).Hidden = False
            If .Cells(1, i) < Date Or .Cells(1, i) > Date + 9 Then .Columns(i).Hidden = True
        Next
    End With
    Application.ScreenUpdating = True

Like you yourself said, change 2 to 3. Don't forget to remove formula in A1 or column will also be hidden.