Unhiding Columns

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,992
Office Version
  1. 2019
Platform
  1. Windows
I use a spreadsheet that the contains the months of the year as headings. I hide all the column headings and each month only unhide the current months heading for Eg Sept 2006

I need to write VBA code that will unhide the column for eg Sept 2006

Your assistance will be most appreciated

Howard
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
You could use Select case for that purpose. This could be linked to a control or set to do it automatically.


HTH


Dave
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,531
Office Version
  1. 365
Platform
  1. Windows
howard

Can you tell us:
1. Which columns are the month headings in? and which row?
2. Exactly what the headings say (at least a couple of them)?
3. Whether those headings are actual text or dates entered in the heading cells but formatted as text?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,992
Office Version
  1. 2019
Platform
  1. Windows
Hi Peter

The dates are in columns B:M in row 4

The dates are formatted as mmmm-yyyyy

I have copied the dates from my spreadsheet below

October-2005 November-2005 December-2005
January-2006 February-2006 March-2006
April-2006 May-2006 June-2006
July-2006 August-2006 September-2006


Howard
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

Hi Howard

If this is pasted into ThisWorkbook module you column will update automatically on opening your workbook. You can change this as you feel necessary to suit your needs, i.e. linking to control etc. You will need to alter the column letters and sheet name and complete the private constants cases for each month

Code:
Option Explicit
 
Private Const Jan As Integer = 1
Private Const Feb As Integer = 2
Private Const Mar As Integer = 3
' Complete for every Month


Private Sub Workbook_Open()
    Dim iMonth As Integer
    Dim MyColumn As Range

    iMonth = DateTime.Month(DateTime.Now())
    
    ' The above will set it automatically
    
With Sheets("Sheet1")
Select Case iMonth
Case Jan:
        Set MyColumn = .Range("B:B")
        'Changing column name to suit your needs
Case Feb:
        Set MyColumn = .Range("C:C")
    'etc etc etc for each month
        End Select
        
.Range("B:M").Select
Selection.EntireColumn.Hidden = True
    
        MyColumn.Select
        Selection.EntireColumn.Hidden = False
End With
End Sub

HTH


Dave
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,992
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave

Thanks for your help

Howard
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,531
Office Version
  1. 365
Platform
  1. Windows
howard

If the dates in row 4 are actually 1 October 2005, 1 November 2005 etc, then try this Worksheet_Activate code

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    Columns("B:M").Hidden = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">For</SPAN> c = 2 <SPAN style="color:#00007F">To</SPAN> 13
        <SPAN style="color:#00007F">If</SPAN> Cells(4, c).Value = DateValue(Now) - Day(Now) + 1 <SPAN style="color:#00007F">Then</SPAN>
            Cells(4, c).EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Forum statistics

Threads
1,141,097
Messages
5,704,314
Members
421,338
Latest member
Pepess

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