# Hiding Columns, based on date

#### dg74

##### New Member
Hi,

Can anyone help me, i'm sure what i need to do is possible, but not sure how.

Basically I have a spreasdheet which has 2 worksheets

one worksheet contains each week end date with a week no

the other sheet contains all 52 weeks of the year in columns, on this sheet is also a week end date.
I want to do a lookup to determine which week of the year it is, then automatically hide the all but 16 of the 52 columns.

Ie if the week end date is apr 8th (week 1), hide columns 17-52, therefore only show columns 1-16 (4 months at a time)

If anyone could help, I would really appreciate it.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

anyone?

#### jmthompson

##### Well-known Member
Let me re-state to be sure I understand-

Sheet 1 contains a list of week-ending dates in one column and the next column contains the week #.

• Do these week #'s match the excel week #'s?
• What cell contains this week#?

Sheet 2 contains a cell with a week-ending date

• Is this validated against the list in sheet 1?
Sheet 2 also has 52-53 columns with week numbers

• which row are these week numbers in?

You want a macro to check the week-ending date in your cell on sheet 2, lookup the assigned week # and then hide all columns except for the 15 to the left of the found week number- showing 16 weeks in all.

• What should happen if the week-ending date is less than 16 weeks into the year, so there are less than 15 columns to the left of your week#?

#### dg74

##### New Member
Hi,

Thanks for the reply.

Yeah sheet 1 contains week numbers in column A (from 1 to 52) and in column B is week ending dates, which is always a friday in my case. so week 1 has a week ending date of friday 8th april

Sheet 2 contains a cell with the current week ending date automatically inserted. then there are 52 columns with the week number on one row and the week ending date on another column.

I want to take the current week ending date, then automatically hide columns based on what week number it is, basically I only want to to show the current week + 16 (ie 4 months at a time). when I get to the last 16 weeks of the year I will just want to show them each week so when I reach december, I will always show Dec 2011, Jan 2012, Feb 2012, Mar 2012 (16 weeks)

I hope this is clear.

The need to do this is the spreadsheet is too big, data gets entered into it each friday, but only for the current week, so I dont need to show the whole 52 weeks at a time.

Thanks

#### jmthompson

##### Well-known Member
The year wrapping complicates matters. At what point will you change sheet1 to start including the following year? And will you recycle week numbers?

#### dg74

##### New Member
once I get to the end of March 2012, all data will be wiped, I will then just input the new week ending dates for 2012-13 and start again.

#### jmthompson

##### Well-known Member
ohhhh, week 1 starts in April 2011. That makes things easier.

#### dg74

##### New Member
yeah, sorry I forgot to mention, I am using the uk financial year, April - March

#### jmthompson

##### Well-known Member
Try this code, in a copy of your file.

The code assumes your curent week-ending date is in "Sheet2", cell "A1", so you may need to update the MyDate line.

It also assumes that your week-ending date/week number lookup values are in "Sheet1" Range "A1:B52". So MyWeek may also need to be updated

Finally, it assumes that your week columns are on "Sheet2" from "B1:BA1". You may need to update these references too.

Code:
``````Sub DisplayMine()
Dim MyWeek As Long
Dim MyDate As Long
Dim c As Range

MyDate = Sheets("Sheet2").Range("A1").Value
MyWeek = Application.VLookup(MyDate, Sheets("Sheet1").Range("A1:B52"), 2, False)
Range("B1:BA1").EntireColumn.Hidden = True

For Each c In Range("B1:BA1")
If c = MyWeek Then
Range(c, c.Offset(, 16)).EntireColumn.Hidden = False
End If
Next c

End Sub``````

#### dg74

##### New Member
thank you so much for this, really appreciate it. I will try it tomorrow and get back to you.

Thanks again

Replies
1
Views
87
Replies
0
Views
143
Replies
2
Views
192
Replies
5
Views
233
Replies
6
Views
128

1,191,213
Messages
5,985,309
Members
439,956
Latest member
FrazzledCat

### 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?

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