Hide all columns that are after this weeks date

[tonywatsonhelp]

Hi Everyone,
Ok so I have a list of dates in column AH16 to CH16 that show every monday of this year in reverse order
(CH16 = 03/01/2022 go to the left with each cell going up 7 days until AH16 = 02/01/23)



What i would like is a macro that can do this,
when i select the page, Find this weeks date (that will be the Monday for the week we are in i.e. 07/02/2022 and hide the columns between AH and that column,

Please help if you can

Thanks

Tony

 

[Candyman8019]

I did something similar once, Here's what I did. In my example A4:A63 contained a list of dates and B1 had the current date. You'll need to modify it to work on weeks rather than a specific date, but this should get you started.

Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlManual
'Hide all rows to start
Rows("4:63").Hidden = True
'Display row(s) that match date criteria
For Each A In Range("A4:A63")
If A.Value = Range("B1").Value Then Rows(A.Row).Offset(-2, 0).Resize(3).Hidden = False
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Activate()
   Dim Rng As Range
   Dim Dt As Date

   Dt = Application.WorkDay_Intl(Date + 1, -1, "0111111")
   Set Rng = Range("AH16:CH16").Find(Dt, , , xlWhole, , xlNext, , , False)
   If Rng Is Nothing Then
      MsgBox "oops"
      Exit Sub
   End If
   Range("AH16:CH16").EntireColumn.Hidden = False
   Range("AH16", Rng.Offset(, -1)).EntireColumn.Hidden = True
End Sub

 

[tonywatsonhelp]

Thank you candyman, your idea was very unique,
And fluff thank you

 

[Fluff]

Glad we could help & thanks for the feedback.