hiding colums

rutgermons
rutgermons used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

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.

Author

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?
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?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

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?
Software Team Lead
Commented:
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
@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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial