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
rutgermonsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:
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.
rutgermonsAuthor Commented:
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 ]IT Services ConsultantCommented:
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?
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Learn More!
rutgermonsAuthor Commented:
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?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try:

Sub test()
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To lastCol
        If IsDate(Cells(1, i)) Then
            If Cells(1, i) < Date Then
                Columns(i).EntireColumn.Hidden = True
            End If
        End If
    Next
End Sub

Open in new window

28696434.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
@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 ]IT Services ConsultantCommented:
You're welcome.

(Sigh)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.