Hiding / Showing columns based a date range

[RSEcon]

Hi there,

I am struggling to figure out a way to show / hide columns based on whether the column header, which is a date, falls within the range specified elsewhere in the sheet. Example scenario below:

Cell C4 (Start Date) contains a dropdown with a list of dates, say 05-Dec, 12-Dec, 19-Dec and 26-Dec
Cell C5 (End Date) contains the same dropdown as that in Cell C4 - i.e. a list of the following dates 05-Dec, 12-Dec, 19-Dec and 26-Dec

Below these two cells, I have a list of headings in Cells E8 through H8, which mirror the values available in the dropdown lists above - i.e. Cell E8 is "05-Dec", Cell F8 is "12-Dec", Cell G8 is "19-Dec" and Cell H8 is "26-Dec". Each of these has a range of numbers in the rows below them.

However, I want to sheet to be smart enough to show only those columns that fall within the range specified by me in cells C4 and C5. So, if C4 (Start Date) is "12-Dec" and C5 (End Date) is "19-Dec", then I want a macro to automatically pick this change up and hide columns E and H in their entirety.

Appreciate any and all your help / assistance in this!

Many thanks!

 

[Osvaldo Palmeiro]

Hi.
Paste this code to the sheet code module. It'll run as you change 'C4' or 'C5' value.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim dt As Range
  If Intersect(Target, Range("C4:C5")) Is Nothing Then Exit Sub
  Columns("E:H").Hidden = False
   For Each dt In Range("E8:H8")
    If dt.Value < [C4] Or dt.Value > [C5] Then Columns(dt.Column).Hidden = True
   Next dt
End Sub

 

[RSEcon]

Ah, this works like a charm - thanks, Osvaldo!