Link to home
Start Free TrialLog in
Avatar of rutgermons
rutgermons

asked on

hiding colums

folks

I have a column A where I show my projects, then columns B to XX with days i.e.

A 1                         B1                   C1             D1
Project                  8 July             9 July          10 July            
Project 1                          
Project 2

how can I hide columns if the are older than today? (i.e. based on the above I only want to see column A and column D i.e.

A 1                         D1                  
Project                  10 July                  
Project 1                          
Project 2


note, I only want to hide the columns, not delete them
Avatar of John
John
Flag of Canada image

I use the Data Tab and then Group Columns. I do this all the time. And hidden columns do not print if the hidden columns are in the print range. Very handy.
Avatar of rutgermons
rutgermons

ASKER

Hi John

Thanks for this, note,I need the hiding of columns based on a condition where a specific cell date must be older than todays date, I don't see that in the Group columns feature to hide based on a specific criteria

can u assist?
Avatar of [ fanpages ]
When should the column(s) with a heading earlier than the current system date be hidden?

Should this be upon opening the workbook, when a heading is changed (edited) or added, or when a manual action is taken (for example, clicking a button)?

If the workbook is open on the cusp of one day to the next (e.g. opened at 11:55pm, & being worked upon beyond midnight), should the column representing 'yesterday' be automatically hidden at 12midnight?
Something like


Private Sub Worksheet_Change(ByVal Target As Range)
    If cells C1:AF 1  older than todays date then
        Columns("C:AF").EntireColumn.Hidden = True
    Else
        Columns("C:AF").EntireColumn.Hidden = False
    End If
End Sub

any ideas?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@fanpages:
When should the column(s) with a heading earlier than the current system date be hidden?

Should this be upon opening the workbook, when a heading is changed (edited) or added, or when a manual action is taken (for example, clicking a button)?

If the workbook is open on the cusp of one day to the next (e.g. opened at 11:55pm, & being worked upon beyond midnight), should the column representing 'yesterday' be automatically hidden at 12midnight?

@rutgermons:
Something like

Private Sub Worksheet_Change(ByVal Target As Range)
    If cells C1:AF 1  older than todays date then
        Columns("C:AF").EntireColumn.Hidden = True
    Else
        Columns("C:AF").EntireColumn.Hidden = False
    End If
End Sub

any ideas?


I would not recommend checking all the columns every time a cell is changed.  I would only check if a cell on row 1 is changed (or a change to a cell within the range you are interrogating [C1:AF1]) but, until you respond to my queries, I will offer a similar approach to what Ryan Chong has provided.

Please consider this as a starting point based on the pseudo code you have provided above:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

  Dim objCell                                           As Range
  
  Application.ScreenUpdating = False
  
' For Each objCell In [C:AF].EntireColumn                                                                                                               ' Alternatively,...
  For Each objCell In Range([C1], Cells(1&, [C:AF].SpecialCells(xlCellTypeLastCell).Column)).EntireColumn
  
      If IsDate(objCell.Cells(1&)) Then
         objCell.Hidden = (objCell.Cells(1&) < Date)
      End If ' If IsDate(objCell.Cells(1&)) Then
      
  Next objCell
  
  Application.ScreenUpdating = False
  
End Sub

Open in new window


For your convenience, a workbook is attached that contains this code.
Q-28696434.xls
You're welcome.

(Sigh)