Help with automatically hiding a range of columns based on date

mits3kgtvr4

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all. I have a spreadsheet with a column for each day in 2021. I am wanting to automatically hide all columns from that range that is NOT the current day. I found some VBA code online but none really met my need. The one that seem to best fit my situation bugged out on me in row 4. This is it below. The only difference I was not to just hide values lower than mine, but all of them except K4. Any help would be appreciated.

TIA!

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice
    Dim xCell As Range
    If Target.Address <> Range("K4").Address Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In Range("A1:I1")
        xCell.EntireColumn.Hidden = (xCell.Value < Target.Value)
    Next
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,480
Office Version
  1. 2013
Platform
  1. Windows
So let me ask a few questions:
So you said:
I have a spreadsheet with a column for each day in 2021

So I assume you have the date Jan 1 2021 in Range "A1"
And Jan 2 2021 in Range "B1" and etc. etc. for 365 columns

So on Jan 1 2020 you only want to see column 1
And on Jan 2 you only want to see column 2
Is that correct?
Now you do know Excel has about 1,500 columns so the other columns like 366 and on will be shown.
Or do you want all 1,500 columns hidden except for just one column which has todays date in row(1)

And we can only hide the entire column we cannot hide just part of the column
And how do you want this script activated
You could just double click on the cell in row 1 and that column would be todays column and then all others would be hidden
 

mits3kgtvr4

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
So let me ask a few questions:
So you said:
I have a spreadsheet with a column for each day in 2021

So I assume you have the date Jan 1 2021 in Range "A1"
And Jan 2 2021 in Range "B1" and etc. etc. for 365 columns

So on Jan 1 2020 you only want to see column 1
And on Jan 2 you only want to see column 2
Is that correct?
Now you do know Excel has about 1,500 columns so the other columns like 366 and on will be shown.
Or do you want all 1,500 columns hidden except for just one column which has todays date in row(1)

And we can only hide the entire column we cannot hide just part of the column
And how do you want this script activated
You could just double click on the cell in row 1 and that column would be todays column and then all others would be hidden
The first column with a date is column F. This macro should only affect the range of columns with a date (F through ??).

I'd like this macro activated automatically upon opening of the file
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Give this a try:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Columns("A:AE").Hidden = True

For Each A In Range("A1:AE1")
If A.Value = Date Then Columns(A.Column).Hidden = False
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 
Solution

mits3kgtvr4

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Columns("A:AE").Hidden = True

For Each A In Range("A1:AE1")
If A.Value = Date Then Columns(A.Column).Hidden = False
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
This worked perfectly! Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,141
Messages
5,628,947
Members
416,354
Latest member
JojoMaque

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top