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

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" wrote:

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,

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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,

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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