sparkytech
Board Regular
- Joined
- Mar 6, 2018
- Messages
- 96
- Office Version
- 365
- 2019
I am creating a 6 week lookahead containing "week beginning" dates for an entire year. There is a drop down where a user can select "Auto Date" and it will start with the current week, or they can select "Manual Date" and input a date. This start date is in cell A6, and in row B18 (from B18 to BA18) I have the "week beginning" dates. Whenever the date in A6 is changed, I would like to unhide everything, and then hide any columns with header dates before the start date, and also anything greater than 6 weeks past that. Basically, I want the sheet to display 6 "weeks" of columns based on the user inputted date. Seems like a simple task, and there is probably a cleaner way to do this. I'm new to VBA, and I can't seem to figure this one out... any help is appreciated!
VBA Code:
Sub Hide_Columns_Containing_Value()
lngStart = Range("Sheet1!A6").Value
lngEnd = Range("Sheet1!A7").Value
Dim c As Range
For Each c In Range("B18:BA18").Cells
If c.Value <= lngStart Or c.Value > lngEnd Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub