Hide columns if header date falls in range

sparkytech

New Member
Joined
Mar 6, 2018
Messages
33
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
 

Attachments

  • 6 Week Lookahead.JPG
    6 Week Lookahead.JPG
    85.2 KB · Views: 5

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
Sub Hide_Columns_Containing_Value()
Dim lngStart As Long, lngEnd As Long
lngStart = Sheets("sheet1").Range("A6").Value
lngEnd = Sheets("sheet1").Range("A7").Value
    
Dim c As Range

    For Each c In Range("B18:BA18")
        If c.Value <= lngStart Or c.Value > lngStart + 40 Then
            c.EntireColumn.Hidden = True

        End If
    Next c

End Sub
 

sparkytech

New Member
Joined
Mar 6, 2018
Messages
33
Thanks! It's sort of working... it hides the header columns when I change dates, but the "tasks" I have in the date columns never "shift" to stay with the correct date. Any ideas?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload Example file & desired Result with XL2BB ADDIN (Preferable) or upload your file at free hosting site e.g. www.dropbox.com, GoogleDrive or Onedrive & input link here.
Why don't use Today() or Now() function at A6
 

sparkytech

New Member
Joined
Mar 6, 2018
Messages
33

ADVERTISEMENT

Please upload Example file & desired Result with XL2BB ADDIN (Preferable) or upload your file at free hosting site e.g. www.dropbox.com, GoogleDrive or Onedrive & input link here.
Why don't use Today() or Now() function at A6
Here you go:

2021 Project Lookahead ~ rev 5.xlsm
ABCDEFGHIJ
1
2
3Auto Date
4Manual Date
5
61/9/2021
72/20/2021
8
9
10
11
12
13Today is: 01/09/2021
14
15
16
17Auto DateJanuary-2021January-2021January-2021January-2021January-2021February-2021February-2021February-2021February-2021
18Input Manual Date: 01/18/20211/3/20211/10/20211/17/20211/24/20211/31/20212/7/20212/14/20212/21/20212/28/2021
19Description
20Task 11815
21Task 22916
22Task 331017
2341118
2451219
2561320
2671421
27
28
29
30
31
32
33
34
35
Sheet1
Cell Formulas
RangeFormula
A6A6=IF(A17="Auto Date",TODAY(),A18)
A7A7=A6+42
A13A13=TODAY()
B17:J17B17=TEXT(B18,"mmmm-yyyy")
B18B18=IF(A17="Manual Date",((((A18+1)+G3)-0)-WEEKDAY((((A18+1)+G3)-0),2)),((TODAY())-WEEKDAY((TODAY()),2)))
C18:J18C18=B18+7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D17Expression=D$17<>C$17textNO
D17Expression=ISEVEN(MONTH(D17))textNO
C17Expression=C$17<>B$17textNO
C17Expression=ISEVEN(MONTH(C17))textNO
J34Dates Occurringthis weektextNO
J34Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I34Dates Occurringthis weektextNO
I34Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H34Dates Occurringthis weektextNO
H34Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B34Dates Occurringthis weektextNO
B34Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B34Expression=(MONTH(B34)<>MONTH(C34))textNO
C34:G34Dates Occurringthis weektextNO
C34:G34Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C34:F34Expression=(MONTH(C34)<>MONTH(D34))textNO
C34:F34Expression=(MONTH(C34)<>MONTH(D34))textNO
J32Dates Occurringthis weektextNO
J32Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I32Dates Occurringthis weektextNO
I32Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H32Dates Occurringthis weektextNO
H32Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B32Dates Occurringthis weektextNO
B32Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B32Expression=(MONTH(B32)<>MONTH(C32))textNO
C32:G32Dates Occurringthis weektextNO
C32:G32Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C32:F32Expression=(MONTH(C32)<>MONTH(D32))textNO
C32:F32Expression=(MONTH(C32)<>MONTH(D32))textNO
J30Dates Occurringthis weektextNO
J30Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I30Dates Occurringthis weektextNO
I30Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H30Dates Occurringthis weektextNO
H30Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B30Dates Occurringthis weektextNO
B30Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B30Expression=(MONTH(B30)<>MONTH(C30))textNO
C30:G30Dates Occurringthis weektextNO
C30:G30Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C30:F30Expression=(MONTH(C30)<>MONTH(D30))textNO
C30:F30Expression=(MONTH(C30)<>MONTH(D30))textNO
J28Dates Occurringthis weektextNO
J28Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I28Dates Occurringthis weektextNO
I28Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H28Dates Occurringthis weektextNO
H28Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B28Dates Occurringthis weektextNO
B28Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B28Expression=(MONTH(B28)<>MONTH(C28))textNO
C28:G28Dates Occurringthis weektextNO
C28:G28Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C28:F28Expression=(MONTH(C28)<>MONTH(D28))textNO
C28:F28Expression=(MONTH(C28)<>MONTH(D28))textNO
J26Dates Occurringthis weektextNO
J26Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I26Dates Occurringthis weektextNO
I26Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H26Dates Occurringthis weektextNO
H26Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B26Dates Occurringthis weektextNO
B26Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B26Expression=(MONTH(B26)<>MONTH(C26))textNO
C26:G26Dates Occurringthis weektextNO
C26:G26Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C26:F26Expression=(MONTH(C26)<>MONTH(D26))textNO
C26:F26Expression=(MONTH(C26)<>MONTH(D26))textNO
J24Dates Occurringthis weektextNO
J24Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I24Dates Occurringthis weektextNO
I24Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H24Dates Occurringthis weektextNO
H24Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B24Dates Occurringthis weektextNO
B24Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B24Expression=(MONTH(B24)<>MONTH(C24))textNO
C24:G24Dates Occurringthis weektextNO
C24:G24Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C24:F24Expression=(MONTH(C24)<>MONTH(D24))textNO
C24:F24Expression=(MONTH(C24)<>MONTH(D24))textNO
J22Dates Occurringthis weektextNO
J22Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I22Dates Occurringthis weektextNO
I22Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H22Dates Occurringthis weektextNO
H22Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B22Dates Occurringthis weektextNO
B22Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B22Expression=(MONTH(B22)<>MONTH(C22))textNO
C22:G22Dates Occurringthis weektextNO
C22:G22Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
C22:F22Expression=(MONTH(C22)<>MONTH(D22))textNO
C22:F22Expression=(MONTH(C22)<>MONTH(D22))textNO
I35:BA35Dates Occurringthis weektextNO
I35:BA35Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H35Dates Occurringthis weektextNO
H35Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
J25Cellcontains a blank value textNO
J25Expression=OR(J$25="None",J$25="none",J$25="N/A",J$25="n/a",J$25="NA",J$25="na")textNO
J27Dates Occurringthis weektextNO
J27Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
J17Expression=J$17<>I$17textNO
J17Expression=ISEVEN(MONTH(J17))textNO
J18:J21Dates Occurringthis weektextNO
J18:J21Expression=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )textNO
I25Cellcontains a blank value textNO
I25Expression=OR(I$25="None",I$25="none",I$25="N/A",I$25="n/a",I$25="NA",I$25="na")textNO
I27Dates Occurringthis weektextNO
I27Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
I17Expression=I$17<>H$17textNO
I17Expression=ISEVEN(MONTH(I17))textNO
I18:I21Dates Occurringthis weektextNO
I18:I21Expression=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )textNO
H25Cellcontains a blank value textNO
H25Expression=OR(H$25="None",H$25="none",H$25="N/A",H$25="n/a",H$25="NA",H$25="na")textNO
H27Dates Occurringthis weektextNO
H27Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
H17Expression=H$17<>G$17textNO
H17Expression=ISEVEN(MONTH(H17))textNO
H18:H21Dates Occurringthis weektextNO
H18:H21Expression=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )textNO
B25:G25Cellcontains a blank value textNO
B25:G25Expression=OR(B$25="None",B$25="none",B$25="N/A",B$25="n/a",B$25="NA",B$25="na")textNO
A17Cell Value="Manual Date"textNO
B17Expression=B$17<>A$17textNO
B17Expression=ISEVEN(MONTH(B17))textNO
B27Dates Occurringthis weektextNO
B27Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B27,B29,B31,B33,B35Expression=(MONTH(B27)<>MONTH(C27))textNO
B27,B29,B31,B33,B35Expression=(MONTH(B27)<>MONTH(#REF!))textNO
B27,B29,B31,B33,B35Expression=(MONTH(B27)<>MONTH(C27))textNO
B27,B29,B31,B33,B35Expression=(MONTH(B27)<>MONTH(#REF!))textNO
C27:G27Dates Occurringthis weektextNO
C27:G27Expression=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )textNO
G17Expression=G$17<>F$17textNO
G17Expression=ISEVEN(MONTH(G17))textNO
F17Expression=F$17<>E$17textNO
F17Expression=ISEVEN(MONTH(F17))textNO
E17Expression=E$17<>D$17textNO
E17Expression=ISEVEN(MONTH(E17))textNO
B19:B21Dates Occurringthis weektextNO
B19:B21Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B19:B21,B23,B25Expression=(MONTH(B19)<>MONTH(C19))textNO
B18:G18,C19:G21,B35:G35,B23:BA23,B25:BA25,B29:BA29,B31:BA31,B33:BA33Dates Occurringthis weektextNO
B18:G18,C19:G21,B35:G35,B23:BA23,B25:BA25,B29:BA29,B31:BA31,B33:BA33Expression=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )textNO
B18:F18,C19:F21,C23:F23,C25:F25Expression=(MONTH(B18)<>MONTH(C18))textNO
B18:F18,C19:F21,C23:F23,C25:F25Expression=(MONTH(B18)<>MONTH(C18))textNO
A18Expression=$A$17="Auto Date"textNO
Cells with Data Validation
CellAllowCriteria
A19:J19Any value
B18Any value
A20:A35Any value
A17List=$A$3:$A$4
A18Datebetween 1/1/1900 and 12/31/3000
 

sparkytech

New Member
Joined
Mar 6, 2018
Messages
33

ADVERTISEMENT

I will have multiple "task" rows for each week (column) on this 6 week lookahead. Say for example, I enter tasks for 6 months. If I display the first 6 weeks of the year, those tasks will be shown under their dates. If I manually enter a date in April, it should move the date range and show the tasks that are in those columns.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
then I think you want worksheet Change event to Whenever you change Date at Cell A6 (I think), it runs and hide unwanted columns.
Are I Correct?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. if you want fixed date at row 18. you should add fixed date at Cell B18 Not Formula to when Change Cell value at A6 or A18 Dates don't Change at Row 18.

2. if you want worksheet_ChangeEvent , go to sheet Name (sheet tab name at bottom at excel Page), Rigt Click, Select View Code
At the right window Delete all code if exists then Paste this.
with this code when you change cell A17 to Auto or Manual Date ( i add Data validationtoit Also) Code Working.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngStart As Long, lngEnd As Long
Application.EnableEvents = False
If Not Application.Intersect(Range("A17"), Target) Is Nothing Then

lngStart = Sheets("sheet1").Range("A6").Value
'lngEnd = Sheets("sheet1").Range("A7").Value
 Range("B1:BA1").EntireColumn.Hidden = False
Dim c As Range

    For Each c In Range("B18:BA18")
        If c.Value + 1 <= lngStart Or c.Value > lngStart + 40 Then
            c.EntireColumn.Hidden = True

        End If
    Next c
End If
Application.EnableEvents = True
End Sub
 

sparkytech

New Member
Joined
Mar 6, 2018
Messages
33
You are correct regarding the worksheet changing dates. The sheet will have columns for 52 weeks, only 6 weeks at a time need to be shown.
The user has (2) options:
1. Select Auto Date - cell B18 will display the current week start date, columns to right of B18 will show the following 5 week beginning dates through G18.
2. Select Manual Date and enter a date manually - cell B18 will display the week start date for the manually entered date, columns to right of B18 will show the following 5 week beginning dates through G18.

It hides the date headers and shows 6 weeks, but the corresponding "task" is not hidden / unhidden. As you can see in the attachments, the numbers in the task columns never move. These columns should be hidden / unhidden based on the date as well. I hope that makes sense.
 

Attachments

  • Current Week.JPG
    Current Week.JPG
    92.2 KB · Views: 1
  • Next month.JPG
    Next month.JPG
    82.8 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,127,008
Messages
5,622,140
Members
415,880
Latest member
Bruce0203

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
Top