Hide a selection of columns within a range

[Grimm127]

Hi All,
I am stuck.
I have a Range of columns Range(C:AA) Each Column Header has a calendar month starting 01/01/2020 to YTD 2020 and continues 01/01/2021 to YTD 2021.
Each month, I need to have a macro that hides a set of columns. For Example:

I select Jan
Columns C (Jan-20); Column L (Oct-20); Column M (Nov-20); Column N (Dec-20) & Column P (Jan-21) will show and the rest within that range will be hidden

If I select Apr
Columns F (Apr-20); Column P (Jan-21); Column Q (Feb-21); Column R (Mar-21) & Column S (Apr-21) will show and the rest within that range will be hidden

I was thinking of using Row 2 as a marker. So if there is an 1(one) in Row 2 within the column range then it hides the column if there is a 0 (zero) in Row 2 then those remain unhidden.

Any suggestions?

Thanks!

 

[Yongle]

With dropdown in A1 (list = ALL, Jan, Feb, ...Dec)
(Code below is case sensitive so ALL works , All does not!)

Selecting Jan automatically returns above result
Select ALL unhides all columns in the range

Place code in the SHEET's code window
- right click on sheet tab \ view code \ paste code into the window that opens up
- it will not work if placed in a standard module or ThisWorkbook code window

Save your workbook as macro enabled

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim M1 As Date, M2 As Date, M3 As Date, M4 As Date, M5 As Date
    Dim TF As Boolean
    Dim c As Long
    If Target.Address(0, 0) = "A1" Then
        If Not Target = "ALL" Then
            M1 = CDate(1 & " " & Target & " " & 2020)
            M2 = DateSerial(Year(M1), Month(M1) + 9, 1)
            M3 = DateSerial(Year(M1), Month(M1) + 10, 1)
            M4 = DateSerial(Year(M1), Month(M1) + 11, 1)
            M5 = DateSerial(Year(M1), Month(M1) + 12, 1)
    
            For c = 3 To 27
                Select Case Cells(1, c)
                    Case M1, M2, M3, M4, M5:    TF = False
                    Case Else:                  TF = True
                End Select
                Columns(c).EntireColumn.Hidden = TF
            Next c
        Else
            Range("C:AA").EntireColumn.Hidden = False
        End If
    End If
End Sub

The code assumes that C1 etc holds a DATE value and that if C1 is selected it shows 1 Jan 2020 in the formula box

Amend to suit your own requirements, but come back if you need help doing that

 

[Grimm127]

This is awesome. WIll try it out when I get to the office. Question though. If I wanted to run this in multiple sheets do I simply apply the code to each sheet as you mentioned above?

 

[Yongle]

If I wanted to run this in multiple sheets do I simply apply the code to each sheet as you mentioned above?

You could do that

OR it could be applied at workbook level, excluding any unwanted sheets
- I will review the code to see what changes are required
- But I will wait until you have tested the above code before posting anything further

 

[Grimm127]

Ok, A couple of issues. I need the drop down field in another cell reference. AB1 is a good cell to use.
Second when I input (Select) Jan it hides all the columns from A:AA, same goes for any other month I put into place

 

[Yongle]

I need the drop down field in another cell reference. AB1 is a good cell to use

Amend one line
If Target.Address(0, 0) = "AB1" Then

when I input (Select) Jan it hides all the columns from A:AA, same goes for any other month I put into place

Does it hide A and B? - seems rather odd - would not expect it to do that

What is the UNDERLYING value in C1?
- is it a date or is it text? The code is looking for a DATE
- what is the value in C1 if it is formatted as a number?

 

[Grimm127]

Oh, I see what I am missing here. The target.address to AB1

My issue is my dates are not starting in C1 but in C3. The first 2 rows are used for headers and what not. What can I change to modify that. It works if I link the dates to C3 to C1. Sweet!

 

[Yongle]

What can I change to modify that. It works if I link the dates to C3 to C1?

Amend

Select Case Cells(3, c)

For clarification:
c here is a variable between 3 and 27
it is NOT column C

 

[Grimm127]

PERFECT! Works Great now. So from here how would you do this in multiple sheets?

 

[Yongle]

You can either copy the code to other sheet code windows (no amendments required)

OR ...... The macro can be amended slightly and placed in ThisWorkbook module so that it applies to all sheets in the workbook
Q Should it apply to all sheets in the workbook or should some be excluded?
- let me know and the new macro (below) can be tailored to allow for any excluded sheets

Here is the amended macro
It is placed somewhere different, its name has changed and it any cell references are now preceded by sh which is the sheet variable

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim M1 As Date, M2 As Date, M3 As Date, M4 As Date, M5 As Date
    Dim TF As Boolean
    Dim c As Long
    If Target.Address(0, 0) = "AB1" Then
        If Not Target = "ALL" Then
            M1 = CDate(1 & " " & Target & " " & 2020)
            M2 = DateSerial(Year(M1), Month(M1) + 9, 1)
            M3 = DateSerial(Year(M1), Month(M1) + 10, 1)
            M4 = DateSerial(Year(M1), Month(M1) + 11, 1)
            M5 = DateSerial(Year(M1), Month(M1) + 12, 1)
       
            For c = 3 To 27
                Select Case Cells(3, c)
                    Case M1, M2, M3, M4, M5:    TF = False
                    Case Else:                  TF = True
                End Select
                Sh.Columns(c).EntireColumn.Hidden = TF
            Next c
        Else
            Sh.Range("C:AA").EntireColumn.Hidden = False
        End If
    End If
End Sub

NOTE
The original code in the sheet code window should be deleted to prevent conflict