Unhiding Columns


Well-known Member
Jun 26, 2006
Office Version
  1. 2021
  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


Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You could use Select case for that purpose. This could be linked to a control or set to do it automatically.


Upvote 0

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?
Upvote 0
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

Upvote 0
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

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
Selection.EntireColumn.Hidden = True
        Selection.EntireColumn.Hidden = False
End With
End Sub


Upvote 0

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>
Upvote 0

Forum statistics

Latest member

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