Hiding / Showing columns based a date range

RSEcon

New Member
Joined
Dec 14, 2016
Messages
3
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Hi.
Paste this code to the sheet code module. It'll run as you change 'C4' or 'C5' value.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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