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
Microsoft Excel

Avatar of undefined
Last Comment
[ fanpages ]

8/22/2022 - Mon
John

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.
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?
[ 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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rutgermons

ASKER
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
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
[ fanpages ]

@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
[ fanpages ]

You're welcome.

(Sigh)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.