hide columns based on date

**mattmorris** · 07-24-2012, 09:57 AM

Hi, on row 4 i have a list of column headers starting at F4 containing the weekending dates (friday). I wish to have a macro which would hide every column apart from the current week and the previous two weeks. I've lookied online and found little to help me with this.

---------- Post added at 08:57 AM ---------- Previous post was at 07:52 AM ----------

anyone got any ideas?

**protonLeah** · 07-24-2012, 06:12 PM

Without a sample workbook I'm only guessing:

Option Explicit

Sub HideColumns()
    Dim CrntDate As Date
    Dim DayNum As Long
    Dim DateFriday As Long
    Dim x As Variant
    Dim BackTwo As Date
    Dim lastcol As Long

    CrntDate = Date
    DayNum = Weekday(CrntDate)
    
    x = 6 - DayNum
    CrntDate = Date + x
    
    BackTwo = CrntDate - 18
    
    lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
    
    Set x = Range("F4", Cells(4, lastcol)).Find(BackTwo)
    x = Split(x.Address, "$")
    
    Columns("F:" & x(1)).EntireColumn.Hidden = True
End Sub

**mattmorris** · 07-25-2012, 03:16 AM

Book1.xlsm

Heres the sheet I'm using, couldn't quite get your code to work.

**protonLeah** · 07-25-2012, 05:08 PM

This one will work if the worksheet only has columns tor Jan - Dec of one year only.

Sub HideColumns()
    Dim TwoWeeksAgo As Long
    Dim ThisWeek    As Long
   
    ThisWeek = WorksheetFunction.WeekNum(Date)
    TwoWeeksAgo = (ThisWeek - 3)
    Columns("F:BE").EntireColumn.Hidden = True
    
    Range(Cells(1, TwoWeeksAgo + 5), Cells(1, ThisWeek + 5)).EntireColumn.Hidden = False
End Sub