stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,862
Office Version
  1. 2019
Platform
  1. Windows
Put this in the Workbook_ Open Event in the VBE

Code:
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

Board Regular
Joined
Jul 12, 2016
Messages
125
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

Code:
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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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

Board Regular
Joined
Jul 12, 2016
Messages
125

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2007
Messages
5,862
Office Version
  1. 2019
Platform
  1. Windows
Which worksheet do you want this to occur in when you open the workbook?
 

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,862
Office Version
  1. 2019
Platform
  1. Windows
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,398
Messages
5,528,503
Members
409,820
Latest member
gabrielrms

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top