Hiding a Column Depending on Date 2

[shytott]

Hi

On my spreadsheet, Cols DL to DW represent the months of this year – with a simple title of J, F, M A etc (ie there is no actual date involved but obviously can be if the solution requires it). The content of the cells is simply a red or green colour determined by Conditional formatting.

What I would like to achieve, is for the months to disappear (hide or make col with=0) when the next month starts - ie no old months are to remain visible. Is there a way of doing this? – the rest of the spreadsheet needs to be unaffected.

Thanks in anticipation

 

[AnotherHiggins]

This will require a macro. Please repost your question in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[shytott]

I thought I had posted this in Forum 707?.
I'd be happy to repost, but I dont want duplicates all over the place.
Can someone confirm Im in teh correct forum here?

Many Thanks

 

[xlbo]

lol - think John's getting a bit trigger happy. You are in the correct forum

To do this, you could use the Sheet ACTIVATE event

You would need to test the NOW function to determine the current month and year

you will also need proper dates in your column headers (the 1st of each month formatted just to mmm should do)

You can then loop through the column headers and test whether the date in each header is < Now. If so, hide the column, if not, make it visible...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shytott]

Phew!
thanks Geoff (I was beginning to wonder as I posted this in the wee hours of this morning!)

At the risk of being completely cap in hand, how would I go about your suggestions? - I'm afraid my excel abilities only just extend beyond Condtional Formating!

Many Thanks

 

[AnotherHiggins]

Oops! I shouldn't post that late, particularly during a big basketball game.

Sorry about that, shytott.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

ok

you need proper dates in your column headers

Would suggest just using 1st of each month

so for Jan 07 use 01/01/07, for feb 07, use 01/02/07 etc etc. You can format these to look like months using custom number format "mmm"

You need a loop. You know your range so you want to loop through the cells collection of that range. syntax for that is

dim c as range

For each c in range("DL1:DW1")

'perform some action

Next c

You also need to test if the range value is less than today's date. That test would look like

If c.value < Now Then
'Hide Column
Else
'Show Column
End if

This would go within the loop

For the code to hide a column - you can get that by using the macro recorder and hiding a column manually so I will let you do that yourself

Use the syntax I have provided and the code you generate to hide a column to come up with a routine that should hide the relevant columns when you run it manually and we can go through how to attach it to an event

A useful thing to know is that a range has a column property so
c.column
will give you the column number of the range you are testing in your loop

Post back if you have any probs or when you have got a working routine that needs to be run automatically

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

If c.value < Now Then

If the column heading is the first of the month, then this test will hide the column of the current month, probably not what the OP wants.

It might be better to use month(now) and month(c.value)

_________________
Bob Rashkin

 

[shytott]

Ok, so heres what I cobbled together, but when I ran it, it makes some of the cells flicker for 2-3 sconds then all settles down to as it was at the start- ie no cols disappear (start date of each month was inserted into row 1)

code:

Private Sub HideCol()
Dim c As Range

For Each c In Range("De1r1")
If c.Value < Now Then
Selection.EntireColumn.Hidden = True
Else
Selection.EntireColumn.Hidden = False
End If

Next c
End Sub


I've put the code into the worksheet 'STC' one of 6 in the workbook.

I'm off for 2 hrs sleep before a night shift, but I will be tackling this later when Im up.

Many thanks
Andy

 

[xlbo]

try this:

Private Sub HideCol()
Dim c As Range
Application.Screenupdating = false
For Each c In Range("De1r1")
If c.Value < int(now) Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If

Next c
End Sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shytott]

Brilliant, we're getting somewhere!

That change worked Geoff, but I had to move the start dates along by one month (ie 1st March is in Febs col - which makes sense of course!). Ive been running this from a button. You mentioned a 'Sheet Activate' command. This sounds like it will run the code when ever the workbook is openned? How would I invoke it from there?

Thanks
Andy

 

[AnotherHiggins]

Wait - why is March 1 in Feb's column?

Anyway - Right Click on the sheet tab and select View Code.

To fire off the macro every time you switch to that given sheet, select Worksheet from the top left drop down over the code area, then select Activate from the top right drop down.

Your code will end up looking like this:

Private Sub Worksheet_Activate()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("a1:g1")
        If c.Value < Int(Now) Then
            c.EntireColumn.Hidden = True
            Else
            c.EntireColumn.Hidden = False
        End If
      
    Next c
End Sub

If you want to fire the macro every time you open the workbook, then Double Click on the This Workbook Project. Select Workbook from the top left drop down and Open from the top right drop down.

In this case, your code will end up looking like this:

Private Sub Workbook_Open()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("a1:g1")
        If c.Value < Int(Now) Then
            c.EntireColumn.Hidden = True
            Else
            c.EntireColumn.Hidden = False
        End If
      
    Next c
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[shytott]

Hi John/ Geoff

If I put 1st march in Febs col, then Feb will remain visible until today becomes 1st Mar and then I dont ned to see it (feb) anymore - well thats my logic!

Im having t oshoot off for my night shift but hopefully the internet police will allow me to access Tektips so I'll have a crck at automating it from there.

Thanks very much so far guys

Andy

 

[xlbo]

As per John for the events

To solve the dates issue:

Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("De1:Dr1")
        If c.Value < Datevalue("01/" & Month(Now) & "/" & year(c.value)) Then
            c.EntireColumn.Hidden = True
            Else
            c.EntireColumn.Hidden = False
        End If
      
    Next c

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shytott]

Geoff / John

I've manged to plant the code in the right place and its doing the job perfectly (im activating it on sheet access).

Its been new territory for me this and I've learned a few things, so thanks very much for your help, I really do appreaciate your time and effort

Andy