Hide Columns

[rickey24]

Hi all

The setup is I have weekdays listed starting with June 1st in C6 goin
horizontal across the whole sheet to IV6. I wanted to do
workbook_open event where it takes whatever today's date is and hide
the columns From C6 through yesterday. Essentially so today's colum
will be right next to the B column. So if today's date is in H6, C6-G
would become hidden when one were to open the workbook.

Thanks so much for the insight.
B

 

[Tom Ogilvy]

Range("C6:IV6").EntireColumn.Hidden = False
res = application.Match(clng(Date-1),Range("C6:IV6"),0)
if not res is nothing then
Range("C6",Range("C6:IV6")(res)).EntireColumn.Hidden = True
End if

 

[rickey24]

Tom

Thanks for the help, but I am getting a 424 error and it i
highlighting the line:

if not res is nothing then

Do you know what this could be?

Thanks

 

[Tom Ogilvy]

my bad

should be

if not iserror(res) then

 

[rickey24]

Tom

Thanks again for the help, works now. Although I have one las
question. Since I only have weekday dates listed in Row C, If today i
Monday then the formula doesn't work. Is there a way, to say if Date
Monday then ...(Clng(Date - 3) else ...(Clng(Date -1). I am not to
worried about opening it up over the weekend. Thanks again

B

 

[Tom Ogilvy]

Dim idex as Long, res as Variant
Range("C6:IV6").EntireColumn.Hidden = False
idex = choose(Weekday(Date,vbSunday),2,3,1,1,1,1,1)
res = application.Match(clng(Date-idex),Range("C6:IV6"),0)
if not res is nothing then
Range("C6",Range("C6:IV6")(res)).EntireColumn.Hidden = True
End if