Unhiding next column

[stroffso]

Hi,

I have a master sheet with Columns dated across starting from 01/07/16 in Cell B1, 02/07/16 in Cell C1 and so on across.

I have charts that are connected with the master sheet and I want it to update each day with only data that is entered so that the charts arent showing loads of empty data. For this reason I have Columns after today hidden, so each day i have to unhide all columns and then hide them again when I am finished.

There are 2 questions here:
Is there any easier way of doing this?
If no to the above is there some code that will unhide all columns and then hide all after todays date?

Sorry if my explanation is poor but would really appreciate any help with this,

thanks

 

[alansidman]

Put this in the Workbook_ Open Event in the VBE

Option Explicit


Private Sub Workbook_Open()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlUp).Column
    Dim i As Long
    For i = 2 To lc
        If Cells(1, i) > Date Then
            Columns(i).Hidden = True
        Else: Columns(i).Hidden = False
        End If
    Next i
End Sub

 

[stroffso]

Thanks for that, It doesnt seem to work though as I need it specifically for one tab. Is there an option to specify the tab and then run the code? Also when you say put in the Workbook_Open Event where is this located?

Put this in the Workbook_ Open Event in the VBE

Option Explicit


Private Sub Workbook_Open()
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlUp).Column
    Dim i As Long
    For i = 2 To lc
        If Cells(1, i) > Date Then
            Columns(i).Hidden = True
        Else: Columns(i).Hidden = False
        End If
    Next i
End Sub

 

[silentwolf]

Hi stroffso,
the workbook Open Event is locatd in your VBE Editor.. douple click on ThisWorkbook I guess it is called...
When you double click on it it automatically opens that event.

HTH

 

[stroffso]

What a very silly question on my part, thanks for clarifying and thanks for original answer too alansidman

Hi stroffso,
the workbook Open Event is locatd in your VBE Editor.. douple click on ThisWorkbook I guess it is called...
When you double click on it it automatically opens that event.

HTH

 

[alansidman]

Which worksheet do you want this to occur in when you open the workbook?

 

[stroffso]

The worksheet is called "Difference Consolidated"

On that sheet I have columns going across for each day of the year showing data relating to that day. I also have charts on another sheet relating to the difference consolidated sheet. So that the charts only show the relevant data up to and including today I hide all the future dated columns. So each day I just need to unhide one of the columns if that makes sense?
thanks

Which worksheet do you want this to occur in when you open the workbook?

 

[alansidman]

Option Explicit


Private Sub Workbook_Open()
    With Sheets("Difference Consolidated")
    Dim lc As Long
    lc = Cells(1, Columns.Count).End(xlUp).Column
    Dim i As Long
    For i = 2 To lc
        If Cells(1, i) > Date Then
            Columns(i).Hidden = True
        Else: Columns(i).Hidden = False
        End If
    Next i
    End With
End Sub