This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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?
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?
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").EntireColu
Else
Columns("C:AF").EntireColu
End If
End Sub
any ideas?
Private Sub Worksheet_Change(ByVal Target As Range)
If cells C1:AF 1 older than todays date then
Columns("C:AF").EntireColu
Else
Columns("C:AF").EntireColu
End If
End Sub
any ideas?
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
@fanpages:
@rutgermons:
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:
For your convenience, a workbook is attached that contains this code.
Q-28696434.xls
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
For your convenience, a workbook is attached that contains this code.
Q-28696434.xls
Premium Content
You need an Expert Office subscription to comment.Start Free Trial