|
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 |
Hiding/unhiding columns based on user selection
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 "Joel" wrote: 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 |
Hiding/unhiding columns based on user selection
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" wrote: 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 |
Hiding/unhiding columns based on user selection
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 "Bob" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 02:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com