Hide Columns Based On Date Value
 

I am currently working on a report that will track and project a consultants
% utilization throughout the year. It consists of a range of columns which
contain week end dates (Fridays) from 1/1/10 €“ 2/25/11 (Row 6, Columns N
through BV). It also has a range of Week Numbers from 1 - 9 (Row 5, Columns N
through BV). The Week Nums correspond the Week End values on row 6.

I have a cell which contains a pick list (L2) for a user to select a mm-yy
value (e.g. Apr-10) or "All". Based on this value, my Worksheet Event code
hides all of the columns in the N:BV range except the columns that contain
the 1st week end date of the chosen month + the next 12 columns. For example,
if Apr-10 is in cell L2, then columns N:Z (Jan €“ Mar) and columns AN:BV (Jul
€“ Feb) are hidden. There is also a conditions that if L2 = "All", that the
entire range of N:BV will be unhidden.

I would like to add a condition to the current Worksheet Event Code that if
L2 = "Hide Prev Wks", then the code will hide ALL of the columns in the N:BV
range except 13 columns based off what value is in cell L3 (Planning Week
Number). Planning Week Number is also a picklist with values from 1 to 53.
For example, if L2 = "Hide Prev Wks" and L3 = 18, then columns N:AD (Weeks 1
- 17) and columns AN:BV (Weeks 31-9) are hidden.

Below is my current Worksheet Event Code. If you would like to see my file
for more context, I would be happy to send over as well. Thank you in advance
for your help.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$L$2" Then
If Target = "All" Then
Range("N:BV").EntireColumn.Hidden = False
Else
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
mr = Range("L2")
ff = mr - Weekday(mr - 6) + 7
fc = Application.Match(CLng(ff), Rows(6), 0)
lf = DateSerial(Year(mr), Month(mr) + 3, 1) -
Weekday(DateSerial(Year(mr), Month(mr) + 3, 2))
lc = Application.Match(CLng(lf), Rows(6))
Columns(14).Resize(, lastcol).Hidden = True
Range(Cells(6, fc), Cells(6, lc)).EntireColumn.Hidden = False
End If
Else
If Not Application.Intersect(Target, Range("N7:BV206")) Is Nothing
Then
On Error Resume Next
Application.EnableEvents = False
Range("M" & Target.Row,
Target).SpecialCells(xlCellTypeBlanks).Value = Target.Value
Application.EnableEvents = True
On Error Goto 0
End If
End If
End Sub

Hide Columns Based On Date Value
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GoBucks" wrote in message
...
I am currently working on a report that will track and project a consultants
% utilization throughout the year. It consists of a range of columns which
contain week end dates (Fridays) from 1/1/10 €“ 2/25/11 (Row 6, Columns N
through BV). It also has a range of Week Numbers from 1 - 9 (Row 5,
Columns N
through BV). The Week Nums correspond the Week End values on row 6.

I have a cell which contains a pick list (L2) for a user to select a mm-yy
value (e.g. Apr-10) or "All". Based on this value, my Worksheet Event code
hides all of the columns in the N:BV range except the columns that contain
the 1st week end date of the chosen month + the next 12 columns. For
example,
if Apr-10 is in cell L2, then columns N:Z (Jan €“ Mar) and columns AN:BV
(Jul
€“ Feb) are hidden. There is also a conditions that if L2 = "All", that the
entire range of N:BV will be unhidden.

I would like to add a condition to the current Worksheet Event Code that
if
L2 = "Hide Prev Wks", then the code will hide ALL of the columns in the
N:BV
range except 13 columns based off what value is in cell L3 (Planning Week
Number). Planning Week Number is also a picklist with values from 1 to 53.
For example, if L2 = "Hide Prev Wks" and L3 = 18, then columns N:AD (Weeks
1
- 17) and columns AN:BV (Weeks 31-9) are hidden.

Below is my current Worksheet Event Code. If you would like to see my file
for more context, I would be happy to send over as well. Thank you in
advance
for your help.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$L$2" Then
If Target = "All" Then
Range("N:BV").EntireColumn.Hidden = False
Else
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
mr = Range("L2")
ff = mr - Weekday(mr - 6) + 7
fc = Application.Match(CLng(ff), Rows(6), 0)
lf = DateSerial(Year(mr), Month(mr) + 3, 1) -
Weekday(DateSerial(Year(mr), Month(mr) + 3, 2))
lc = Application.Match(CLng(lf), Rows(6))
Columns(14).Resize(, lastcol).Hidden = True
Range(Cells(6, fc), Cells(6, lc)).EntireColumn.Hidden = False
End If
Else
If Not Application.Intersect(Target, Range("N7:BV206")) Is Nothing
Then
On Error Resume Next
Application.EnableEvents = False
Range("M" & Target.Row,
Target).SpecialCells(xlCellTypeBlanks).Value = Target.Value
Application.EnableEvents = True
On Error Goto 0
End If
End If
End Sub