Auto hide columns based on current date

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,540
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a planner i am using and wondered if it is possible to have 6 weeks Mon-Fri only visable based on the current date

So in column K started way back in 06/08/20 then this runs to AHO 31/12/23

The current date is in D2 =now()

Could any columns before and after 6 weeks be hidden based on cell D2

Many thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Add this into the Workbook Event for On Open and it should do what you want regardless of the date in D2. In the workbook use Alt + F11 to open VBA on the left you will see ThisWorkbook double click this and paste the code in, you may need to adjust the sheet name.

VBA Code:
Private Sub Workbook_Open()
lCol = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
'Start from Column B
For i = 2 To lCol
'Use this line to hide all dates up to current and upto 42 days after
'Adjust the numbers to fit your needs.
If Cells(1, i).Value < Date Or Cells(1, i) > Date + 42 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next

End Sub
 
Upvote 0
Hi Trevor G,

Many thanks for your help,but i cannot get this code to work?

Nothing seems to happen at all

Thanks again for trying to help!
 
Upvote 0
Which row are your dates on & what is the sheet name?
 
Upvote 0
Ok how about
VBA Code:
Private Sub Workbook_Open()
   Dim i As Long
   With Worksheets("Live Orders")
      .Cells.EntireColumn.Hidden = False
      For i = 11 To .Cells(2, Columns.Count).End(xlToLeft).Column
         If .Cells(2, i).Value < Date - 42 Or .Cells(2, i) > Date + 42 Then
            .Columns(i).Hidden = True
         ElseIf Weekday(.Cells(2, i), 2) > 5 Then
            .Columns(i).Hidden = True
         End If
      Next
   End With
End Sub
 
Upvote 0
HI Fluff,

Almost there, this works but only shows Monday,Tuesday and Friday, seems to hide every wednesday and Thursday?

Also can this be modified to show 2 weeks before todays date also

Many thanks
 
Upvote 0
This will show 2 weeks prior to today, rather than 6
VBA Code:
Private Sub Workbook_Open()
   Dim i As Long
   With Worksheets("Live Orders")
      .Cells.EntireColumn.Hidden = False
      For i = 11 To .Cells(2, Columns.Count).End(xlToLeft).Column
         If .Cells(2, i).Value < Date - 14 Or .Cells(2, i) > Date + 42 Then
            .Columns(i).Hidden = True
         ElseIf Weekday(.Cells(2, i), 2) > 5 Then
            .Columns(i).Hidden = True
         End If
      Next
   End With
End Sub
Not sure why it's not showing Wed & Thu, as it does for me
+Fluff New.xlsm
CJCMCNCOCPCQCTCUCVCWCX
1
217/07/202020/07/202021/07/202022/07/202023/07/202024/07/202027/07/202028/07/202029/07/202030/07/202031/07/2020
3FriMonTueWedThuFriMonTueWedThuFri
Master
 
Upvote 0
Hi all,

Could the following formula be modified to suit the working week rather than individual days
Code:
Private Sub Workbook_Open()
lCol = Worksheets("Live Service Orders").Cells(1, Columns.Count).End(xlToLeft).Column
'Start from Column B
For i = 21 To lCol
'Use this line to hide all dates up to current and upto 42 days after
'Adjust the numbers to fit your needs.
If Cells(1, i).Value < Date Or Cells(1, i) > Date + 30 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next

End Sub

Thanks for any help
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top