Hide a selection of columns within a range

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
With dropdown in A1 (list = ALL, Jan, Feb, ...Dec)
(Code below is case sensitive so ALL works , All does not!)

HideColumns.jpg


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

VBA Code:
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

Board Regular
Joined
Aug 17, 2011
Messages
118
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 17, 2011
Messages
118

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 17, 2011
Messages
118

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
What can I change to modify that. It works if I link the dates to C3 to C1?
Amend
Rich (BB code):
Select Case Cells(3, c)

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

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
PERFECT! Works Great now. So from here how would you do this in multiple sheets?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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


ThisWorkbookCode.jpg



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

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

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