Hiding/unhiding columns based on user selection

[Bob]

I have pairs of columns representing Mgmt. and Non-Mgmt. Hours for each month
of 2007 and 2008, starting in column J. For example, columns P and Q contain
the hours for Mgmt. and Non-Mgmt for April 2007, respectively.

I also have a drop-down list box where a user can select the Last-Month/Year
of data they want to see. The choices displayed in the drop-down list box
come from a 1-column range called Choices_End_Period.

I would like to write a macro that automatically hides the columns of data
beyond the Last-Month/Year that was selected. For example, if a user selects
June 2007, columns V – BE would automatically be hidden. However, if a user
changed their mind and selected October 2008, columns V – BA would
automatically be unhidden, but columns BB – BE would remain hidden. Etc.

Since I am a novice when it comes to writing VBA code, I would greatly
appreciate any help.

Thanks,
Bob

 

[Joel]

Sub hidecoluumns()

'sample date for testing
selecteddate = DateValue("12/07/07")

For LastColumn = Columns.Count To 10 Step -1
If Not IsEmpty(Cells(1, LastColumn).Value) Then
Exit For
End If
Next LastColumn

For columncount = 10 To LastColumn

If Cells(1, columncount).Value > selecteddate Then
Cells(1, columncount).EntireColumn.Hidden = True
Else
Cells(1, columncount).EntireColumn.Hidden = False

End If
Next columncount

End Sub

 

[Bob]

Joel,

Thanks for the help! Unfortunately, when I ran your macro, columns BI thru
BL, and BN thru BP became hidden. The last column for inputting data in my
worksheet is column BE (December 2008 Non-Mgmt.) (although I do have various
"lookup tables" located in columns BI thru BP).

Bob

 

[Joel]

How can we tell when the dates stop? Is the a blank column after the last
date column? Or can we stop when we get to column BL? The code need a minor
modification to determine where the last cell is. I think a test for a date
may help try this code below. Code stops when it doesn't find a date in row
1.


Sub hidecoluumns()

'sample date for testing
selecteddate = DateValue("12/07/07")

For LastColumn = Columns.Count To 10 Step -1
If Not IsEmpty(Cells(1, LastColumn).Value) Then
Exit For
End If
Next LastColumn

For columncount = 10 To LastColumn
If IsDate(Cells(1, columncount).Value) Then
If Cells(1, columncount).Value > selecteddate Then
Cells(1, columncount).EntireColumn.Hidden = True
Else
Cells(1, columncount).EntireColumn.Hidden = False

End If
Else
Exit For
End If
Next columncount

End Sub

 

[Bob]

Joel,

Actually, I think you would want to start hiding columns with the first
blank date cell. I have rigged it whereby if a user selects March 2008, for
example, then in row 4 I have a formula that automatically populates January
2007 (in columns J & K), February 2007 (in columns L & M) etc. until it gets
to April 2008. From that point on, my formula evaluates to blank (i.e., all
cells in row 4 to the right of March 2008 are set to blank).

Since I know the last Month-Year will always be in cells BD4 and BE4, I know
the point at which to stop hiding columns (even though the date in cells BD4
and BE4 may be set to blank because a user selected a Month-Year value that
is earlier than December 2008).

So using the previous example, I would expect the macro to automatically
hide column AN (i.e., the first blank date which is in cell AN4) through
column BE (i.e., which will always be the last column for inputting Hours).

Does this help clarify things?

Thanks,
Bob

 

[Joel]

The code I sent in my last posting uses isdate to determine when to stop. A
blank cell will also cause this code to stop.

 

[Bob]

Then why did the macro hide columns columns BI thru BL, and BN thru BP when I
ran it, which are not even in the range of columns J thru BE (which is where
all the data is located)?

If it helps, I have now written a formula which identifies the last column
that should be visible, and I named the cell "Last_Displayed_Column".

Is it possible to write a macro that simply uses the cell reference
contained in "Last_Displayed_Column", add 1, and then hide the columns from
that point on to column BE? For example, if "Last_Displayed_Column" = $AM$4
(which equates to column # 39), then the macro would hide columns 40 thru 57.

Thanks,
Bob

 

[Bob]

Joel,

What I'm looking for is something like this:

Sub HideUnhideColumns()
Application.ScreenUpdating = False
ActiveSheet.Columns("N:BE").EntireColumn.Hidden = False
ActiveSheet.Columns("ZZZ:BE").EntireColumn.Hidden = True
Application.ScreenUpdating = True
End Sub

I just don't know how to substitute the value of a variable for ZZZ (refer
to my previous post).

Thanks,
Bob

 

[Joel]

bob: this code gets a cell address and then gets the column letter from the
address.

Sub hide()

celladdr = Range("A5").Address
'remove the column leeter from row number
columnletter = Mid(celladdr, 2)
columnletter = Left(columnletter, InStr(columnletter, "$") - 1)

ActiveSheet.Columns(columnletter & ":BE").EntireColumn.Hidden = True

End Sub