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!
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
It should affect a handful of tabs.
Tab Names: Income Statement, Maintenance, Safety and Finance
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
It should affect ....
Income Statement, Maintenance, Safety and Finance

VBA 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
    
    Select Case Sh.Name
        Case "Income Statement", "Maintenance", "Safety", "Finance"
            
            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 Select
End Sub
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
Hi, So far it works very well. However, I am getting an error below when I select All. I thought "All" would unhide the entire field. Aside from that it's fantastic. The second error is strange and happens when I first start using the macro.
 

Attachments

  • Screenshot 2021-02-23 110831.jpg
    Screenshot 2021-02-23 110831.jpg
    11.2 KB · Views: 1
  • Screenshot 2021-02-23 111503.jpg
    Screenshot 2021-02-23 111503.jpg
    10.1 KB · Views: 1

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
The second error is because VBA can see TWO (or more) macros with the same name Printing_Format
This often happens when testing - search all code windows for a macro with that name and make sure you delete the correct one!
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118

ADVERTISEMENT

Ok, I found the duplicate and removed it. Let me know about the ALL when you can. That is the last piece of the puzzle. Thank you so much for your guidance. I have been playing with VBA many years but this is the first time I ever used it imbedded within the worksheets.
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
Thank you everything works. The last question that I have is this. Can the AB1 Reference be in another Tab completely separate from the tabs that it refreshes?

What if I want to Use the AB1 Cell in Tab "Driver"? I would like to do a Data Validation List box in that AB1 cell. Will that work?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is this your amended requirement?
Changing selection in dropdown in cell AB1 in "Driver" should simultaneously trigger change to visible columns in 4 tabs
( Income Statement, Maintenance, Safety and Finance)
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
Is this your amended requirement?
Changing selection in dropdown in cell AB1 in "Driver" should simultaneously trigger change to visible columns in 4 tabs
( Income Statement, Maintenance, Safety and Finance)
Exactly. I tried to do a Link to the Driver Tab to each of the AB1 Cells using a List. But what happens is when I switch the Months in the Driver Tab it doesn't do anything in the other four tabs. It only works if I manually type in a month and then enter
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
In that case, the change in cell AB1 in sheet "Driver" becomes the trigger
I will post amended code tomorrow afternoon
 

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
In that case, the change in cell AB1 in sheet "Driver" becomes the trigger
I will post amended code tomorrow afternoon
Thank you for your time and patience. I am definitely learning something new and thankful.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,835
Messages
5,627,158
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