Auto Hide Columns based on passed Dates!!!

[MarkReddell]

Good Morning 2 ALL!!! I need help with macro to auto hide all columns w/passed dates! Starting in F5 which has the date 1/1/2018, to the end of W/S columns always in row 5. All dates are progressive to the end of W/S Columns! Is this possible??? Thanks for any help!!!

 

[MarkReddell]

By the Way, my current W/S is Called Master Show Schedule!!!

 

[My Aswer Is This]

So if you enter a date less then Today into Row(5) you want that column Hidden.
Is that what you want.

So Today for example you enter 8/7/18 into Range("F5") the column F would be hidden.

And this feature would work in any column Greater then Column 5

 

[My Aswer Is This]

If the answers to my questions are yes.
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/22/2018  3:44:20 AM  EDT
If Target.Column > 5 And Target.Row = 5 Then
If Target.Value < Date Then Columns(Target.Column).Hidden = True
End If
End Sub

 

[MarkReddell]

Good morning Sir! I tried it but it didn't work, Maybe because the date cells starting in "F5" begin with 1/1/2018 & progressively move to "G5" with 1/2/2018 etc. to the end of all the Columns in w/s. I need to have the auto macro to hide any & all columns that have a date that is in the past. So, currently, I have 8 months & 21 days that the macro needs to auto. hide because today is 9/22/2018 & every date Column in row 5 in the future that becomes yesterday. Thanks again for your help!!!

 

[My Aswer Is This]

Try this.
To activate the script you must enter date in Row 5 column (5) or greater with a date less then today.
This date must be manually entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/22/2018  10:03:18 AM  EDT
If Target.Column > 5 And Target.Row = 5 Then
Dim Lastcolumn As Long
Lastcolumn = Cells(5, Columns.Count).End(xlToLeft).Column
Dim r As Range
For Each r In Cells(5, 6).Resize(, Lastcolumn)
If r.Value < Date Then Columns(r.Column).Hidden = True
Next
End If
End Sub

 

[MarkReddell]

Thanks so much for all your help! However, all of my dates in columns in row 5 are all in there by formulas. I need somehow to do macro auto hide columns based on all current dates in the past. This maybe can't be done! Thanks again, kind sir!!!

 

[My Aswer Is This]

I know of no script that will run automatically to look in all columns and see if the date in row 5 is less then todays date.
To run the script all you need to do is enter a date less then today into row 5 with a column greater then 5.

Now we could have the script run every time you activate the sheet. But very few scripts run just automatically. There must be some action to take place for the script to run. And a formula change is not one of those. Or if it can be done I do not know how to do that.

There are always others here on this forum who see these post so maybe someone else here will provide a answer.

I will continue to monitor this thread to see what I can learn

 

[James006]

@ My Aswer Is This

Hello,

You are right ... besides the change event, you could be using the activate event and the calculate event ...

Depending on the sheet configuration and the way the user interacts with his work ...he should pick what suits him best ...

Hope this will help

 

[My Aswer Is This]

I'm not sure how calculate event works or how to write one.
It would seem like a waste of resources to have a calculation event need to look into all cells in Row 5 starting in column 5 to the last column with data every time something on the sheet calculates.
I would then think the user may be upset when things slow down.

But I'm watching to see who has a answer here.