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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
714
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,866
Messages
5,834,073
Members
430,260
Latest member
MANICX100

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