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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,568
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You should fix date at B18 to task numbers hidden, because is B18 not fixed when changed date at auto date or manual date the Cell B18 changed then Cell C18 to Ba 18 Changed Based on. But with this date change at row header, task number fixed and then if your date changed from 1/9/2021 to 21/2/2021 then the first cell at row B18 show 21/2/2021 but at that column task numbers show the values that shows for previous date.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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