Hide Columns automatically based on date

[chrisjc19]

Dear all,

I have a problem where I want to hide certain whole columns automatically where a date in the cells between B4:I4 is greater than the 'current date' in cell A3 [i.e. cell A3 has the value of "=NOW()"] and to show those same whole columns automatically where a date in the cells between B4:I4 is less than the current date.

That is, for example:

A3 =
2 December 2011 at 4:45 PM

B4= C4= D4= E4= F4= G4= H4= I4=
1-Apr-11 1-May-11 1-Jul-11 1-Sep-11 1-Oct-11 1-Dec-11 1-Feb-12 1-Apr-12

So in the the above example, whole columns H:I should hide as the value in the cells in each of H4:I4 is greater than the current date in A3 whereas columns B:G should show as the value in the cells in each of B4:G4 is less than the current date in A3.

Does anyone know how this can be achieved (I assume a macro/VB script is required)? And how it can be achieved automatically (i.e. much the same as formulas update automatically)?

Thanks in advance!

Best regards,
Chris

[arlu1201]

Use this code. Right click on the sheet you are using and click on View Code. Copy this code in there. It will work each time the sheet is activated.

Please Login or Register  to view this content.

[royUK]

Avoid loops whenever possible

Place this code in a Standard Module

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

Please Login or Register  to view this content.

Trigger it with the WorkSheet Change event

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

Please Login or Register  to view this content.

[royUK]

I've amended the code to allow for the dates not starting with January.

Please Login or Register  to view this content.

[chrisjc19]

Thank you both for your help.

I tried both.

For Ardette's solution, it hides dates after the date in A3, but then when the date in A3 changes to a later it does not re-show the columns with dates before the date in A3.

For Roy's solution, it does not appear to update the sheet automatically. It does work when I run the macro manually. However, it hides some dates but does not hide columsn for all dates after the date in A3?

Not sure how I can resolve this, but I can try experimenting with both solutions to see if it can be resolved?

[chrisjc19]

Perhaps if I give an example spreadsheet this will help and the formula can be put into this spreadsheet?

Having trouble uploading though?!

Thanks again

[chrisjc19]

I think it may have something to do with this formula in the viewcode part:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
HideCols
End Sub

I've changed to A3 to match my spreadsheet, but it doesn't seem to update automatically still.

[royUK]

My final example hides the months after the date in the specified cell

[chrisjc19]

Thanks Roy - I still cannot get it to work. I have attached a sample file in which I have deleted all personal information but where the information is the same as my actual document. I want to be able to change the date in A3 and the columns show/hide depending on whether the date is before/after the date in A3 respectively.

Any help you can give me is greatly appreciated as for an important work project...! Thanks for all your help so far

Chris

[royUK]

Your attached file doesn't have consecutive months, there's months missing.

I'm not really sure what you are doing

[chrisjc19]

Hi Roy,

As per my original email where I set out the first several dates, the months are not all months as, for what I am working on, there is only data for every 2-3 months.

Do you know how to write the formula such that it hides/shows the columns relative to A3? Your spreadsheet that you sent seems to work well so if the same thing can be achieved for this spreadsheet given your excellent VBA knowledge, that would be great

So, just to clarify, it would be to hide all columns where the dates are after the dates in A3, no matter what I enter into A3, and then to show/hide columns depending on what is entered into A3. In the spreadsheet, I've put a little explanation if that assists you?

Thanks again!

Chris