Hiding Columns based on cell value

  • Thread starter Thread starter GoBucks
  • Start date Start date
G

GoBucks

I am a novice when it comes to writing VBA code, I would greatly
appreciate any help I can get in figuring out my ?.

I would like to write a macro that automatically hides columns of data
based on the value of a cell (I2) with a picklist. Cell I2's picklist is
monthly values (formatted as Jan-10 though Dec-10 but real values are
1/1/2010 through 12/1/2010). I have a range that contains work week end date
values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro
hide columns that are less than date value chosen in I2.

For example, if a user selects "Mar-10" in cell I2, then columns L – S would
automatically be hidden. However, if a user changed their mind and selected
"Jun-10", columns L – AF would automatically be hidden.

Thank you in advance for any help given.
 
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.
 
Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAid Software
If Target.Address <> Range("i2").Address Then Exit Sub
'MsgBox Month(Target)
If Target = "All" Then
Columns.Hidden = False
Else
lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
mr = Range("i2")
'MsgBox mr
ff = mr - Weekday(mr - 6) + 7
'MsgBox ff
fc = Application.Match(CLng(ff), Rows("6:6"), 0)
'MsgBox fc

lf = DateSerial(Year(mr), Month(mr) + 3, 1) - Weekday(DateSerial(Year(mr),
Month(mr) + 3, 2))
'MsgBox lf
lc = Application.Match(CLng(lf), Rows("6:6"))
'MsgBox lc

Columns(12).Resize(, lastcol).Hidden = True
Range(Cells(6, fc), Cells(6, lc)).EntireColumn.Hidden = False
End If
End Sub
 
Back
Top