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
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
Last Comment
[ fanpages ]
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.
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?
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
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?
ASKER CERTIFIED SOLUTION
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
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:
@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
You're welcome.
(Sigh)
(Sigh)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.