Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Hi All........
I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Are your month numbers in consecutive order through the range B25:AW25 like
this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Hi Rick.........thanks for your interest.
My numbers are all in consecutive order, about eight years worth, but may not contain all the months for each year. For instance, the first year might show 1 ,2,3,6,7,9,12 and the second year might show 1,3,6,9,11, etc etc. What they are are months in which I have had Blood Tests taken, and they are not always the same each year. What I want to see is, if I select 2 according to my above date, I would expect to see all columns with 1,2,3 in row 25 from the first year and 1,3 from the second and all other columns hidden. What I'm doing is charting this data and want to observe my BloodTest readings for a particular time of the year to see if any of the readings are seasonal. I'm therefore looking for columns with the specific month numbers in them and not just the column next to the one of my choice......I may even have data for two occasions in the same month...both would have the same number in row 25. Hope this helps Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Are your month numbers in consecutive order through the range B25:AW25 like this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Try this macro (change the worksheet reference to your actual worksheet
name); it will ask you for the month value to process and then handle all the necessary column hiding/unhiding as required ... Sub HideColumns() Dim X As Long, Answer As String Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False For X = 2 To 49 If .Cells(25, X).Value < Answer - 1 Or _ .Cells(25, X).Value Answer + 1 Then .Columns(X).Hidden = True End If Next Application.ScreenUpdating = True End With End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Hi Rick.........thanks for your interest. My numbers are all in consecutive order, about eight years worth, but may not contain all the months for each year. For instance, the first year might show 1 ,2,3,6,7,9,12 and the second year might show 1,3,6,9,11, etc etc. What they are are months in which I have had Blood Tests taken, and they are not always the same each year. What I want to see is, if I select 2 according to my above date, I would expect to see all columns with 1,2,3 in row 25 from the first year and 1,3 from the second and all other columns hidden. What I'm doing is charting this data and want to observe my BloodTest readings for a particular time of the year to see if any of the readings are seasonal. I'm therefore looking for columns with the specific month numbers in them and not just the column next to the one of my choice......I may even have data for two occasions in the same month...both would have the same number in row 25. Hope this helps Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Are your month numbers in consecutive order through the range B25:AW25 like this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Pretty awesome code, most cool..........many many thanks.
Any idea how to get it to "round the corner" and return January months for a December selection and viceversa? Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Try this macro (change the worksheet reference to your actual worksheet name); it will ask you for the month value to process and then handle all the necessary column hiding/unhiding as required ... Sub HideColumns() Dim X As Long, Answer As String Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False For X = 2 To 49 If .Cells(25, X).Value < Answer - 1 Or _ .Cells(25, X).Value Answer + 1 Then .Columns(X).Hidden = True End If Next Application.ScreenUpdating = True End With End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Hi Rick.........thanks for your interest. My numbers are all in consecutive order, about eight years worth, but may not contain all the months for each year. For instance, the first year might show 1 ,2,3,6,7,9,12 and the second year might show 1,3,6,9,11, etc etc. What they are are months in which I have had Blood Tests taken, and they are not always the same each year. What I want to see is, if I select 2 according to my above date, I would expect to see all columns with 1,2,3 in row 25 from the first year and 1,3 from the second and all other columns hidden. What I'm doing is charting this data and want to observe my BloodTest readings for a particular time of the year to see if any of the readings are seasonal. I'm therefore looking for columns with the specific month numbers in them and not just the column next to the one of my choice......I may even have data for two occasions in the same month...both would have the same number in row 25. Hope this helps Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Are your month numbers in consecutive order through the range B25:AW25 like this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Sorry, I forgot about your wrap-around requirement; try this macro
instead... Sub HideColumns() Dim X As Long, Answer As String, Test As String Const Search As String = ",12,01,02,03,04,05,06,07,08,09,10,11,12,01," Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If On Error GoTo FixTheDisplay With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False Test = Mid(Search, InStr(4, Search, "," & _ Format$(Answer, "00") & ",") - 3, 10) For X = 2 To 49 If InStr(Test, "," & Format(.Cells(25, _ X).Value & ",", "00")) = 0 Then .Columns(X).Hidden = True End If Next End With FixTheDisplay: Application.ScreenUpdating = True End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Pretty awesome code, most cool..........many many thanks. Any idea how to get it to "round the corner" and return January months for a December selection and viceversa? Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Try this macro (change the worksheet reference to your actual worksheet name); it will ask you for the month value to process and then handle all the necessary column hiding/unhiding as required ... Sub HideColumns() Dim X As Long, Answer As String Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False For X = 2 To 49 If .Cells(25, X).Value < Answer - 1 Or _ .Cells(25, X).Value Answer + 1 Then .Columns(X).Hidden = True End If Next Application.ScreenUpdating = True End With End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Hi Rick.........thanks for your interest. My numbers are all in consecutive order, about eight years worth, but may not contain all the months for each year. For instance, the first year might show 1 ,2,3,6,7,9,12 and the second year might show 1,3,6,9,11, etc etc. What they are are months in which I have had Blood Tests taken, and they are not always the same each year. What I want to see is, if I select 2 according to my above date, I would expect to see all columns with 1,2,3 in row 25 from the first year and 1,3 from the second and all other columns hidden. What I'm doing is charting this data and want to observe my BloodTest readings for a particular time of the year to see if any of the readings are seasonal. I'm therefore looking for columns with the specific month numbers in them and not just the column next to the one of my choice......I may even have data for two occasions in the same month...both would have the same number in row 25. Hope this helps Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Are your month numbers in consecutive order through the range B25:AW25 like this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide certain columns in range
Fine, fine SuperFine Rick............
Besides being a good macro that does exactly what I want in this situation, there's much I can learn from your code..... many thanks Rick, I appreciate! Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Sorry, I forgot about your wrap-around requirement; try this macro instead... Sub HideColumns() Dim X As Long, Answer As String, Test As String Const Search As String = ",12,01,02,03,04,05,06,07,08,09,10,11,12,01," Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If On Error GoTo FixTheDisplay With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False Test = Mid(Search, InStr(4, Search, "," & _ Format$(Answer, "00") & ",") - 3, 10) For X = 2 To 49 If InStr(Test, "," & Format(.Cells(25, _ X).Value & ",", "00")) = 0 Then .Columns(X).Hidden = True End If Next End With FixTheDisplay: Application.ScreenUpdating = True End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Pretty awesome code, most cool..........many many thanks. Any idea how to get it to "round the corner" and return January months for a December selection and viceversa? Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Try this macro (change the worksheet reference to your actual worksheet name); it will ask you for the month value to process and then handle all the necessary column hiding/unhiding as required ... Sub HideColumns() Dim X As Long, Answer As String Answer = Application.InputBox("Which month number?", _ "Get Month Number", Type:=1) If Answer < 1 Or Answer 12 Then MsgBox "Bad month number... run the macro again." Exit Sub End If With Worksheets("Sheet6") Application.ScreenUpdating = False .Columns.Hidden = False For X = 2 To 49 If .Cells(25, X).Value < Answer - 1 Or _ .Cells(25, X).Value Answer + 1 Then .Columns(X).Hidden = True End If Next Application.ScreenUpdating = True End With End Sub -- Rick (MVP - Excel) "clr" wrote in message ... Hi Rick.........thanks for your interest. My numbers are all in consecutive order, about eight years worth, but may not contain all the months for each year. For instance, the first year might show 1 ,2,3,6,7,9,12 and the second year might show 1,3,6,9,11, etc etc. What they are are months in which I have had Blood Tests taken, and they are not always the same each year. What I want to see is, if I select 2 according to my above date, I would expect to see all columns with 1,2,3 in row 25 from the first year and 1,3 from the second and all other columns hidden. What I'm doing is charting this data and want to observe my BloodTest readings for a particular time of the year to see if any of the readings are seasonal. I'm therefore looking for columns with the specific month numbers in them and not just the column next to the one of my choice......I may even have data for two occasions in the same month...both would have the same number in row 25. Hope this helps Vaya con Dios, Chuck, CABGx3 "Rick Rothstein" wrote in message ... Are your month numbers in consecutive order through the range B25:AW25 like this? 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4...etc Also, do you want all the month numbers you indicated to remain visible or just the first one in the list of month numbers? -- Rick (MVP - Excel) "CLR" wrote in message ... Hi All........ I have a Range named "Months" which is B25:AW25. Each cell in that range contains a number of 1-12 standing for a month. I would like to be able to hide all columns except the Month number I choose, and the one on each side of it. For example, if I choose the month 7, then I want to hide all columns except those with a 6,7, or 8 in my range. And of course if I choose month 1, I want all except 12, 1, and 2 to be hidden, etc. TIA for any ideas Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide a range of columns without hiding charts there? | Charts and Charting in Excel | |||
Hide columns & rows that contain "0" or blank in a range of cells | Excel Worksheet Functions | |||
Prog. Hide range and indiv. columns? | Excel Programming | |||
How to hide a Range of Columns? | Excel Programming | |||
How to hide a Range of Columns? | Excel Programming |