| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns in code
I have this Worksheet event that I am trying to use to hide rows and columns
based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding Rows and Columns in code
First, nothing to do with your question...
Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False could be replaced with: Me.Rows("6:17").EntireRow.Hidden = False Me.Columns("D:AB").EntireColumn.Hidden = False (And the same with the other .select/selection.entirecolumn... stuff, too.) There's not too many things that code needs to select first before working with them. Second, This is a guess... I'm guessing that you have merged cells in your worksheet. And you may find that hiding a cell that is merged with cells in columns that are hidden will hide all the columns in that merged area (this behavior changes with versions of excel, though). What version of excel has the problem for you. Ayo wrote: I have this Worksheet event that I am trying to use to hide rows and columns based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding Rows and Columns in code
I am using Excel 2007 but the file is saved as 97-2003. A2:K2 are merged, and
when I manually hide columns in this range it works fine but when I run it through the code it hides column A through K. "Dave Peterson" wrote: First, nothing to do with your question... Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False could be replaced with: Me.Rows("6:17").EntireRow.Hidden = False Me.Columns("D:AB").EntireColumn.Hidden = False (And the same with the other .select/selection.entirecolumn... stuff, too.) There's not too many things that code needs to select first before working with them. Second, This is a guess... I'm guessing that you have merged cells in your worksheet. And you may find that hiding a cell that is merged with cells in columns that are hidden will hide all the columns in that merged area (this behavior changes with versions of excel, though). What version of excel has the problem for you. Ayo wrote: I have this Worksheet event that I am trying to use to hide rows and columns based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding Rows and Columns in code
And if you unmerge those cells, does your code work ok?
If it does, I think you have two choices. Live with the merged cells and the code not working the way you want or remove the merged cells permanently and have the code work the way you want. Ayo wrote: I am using Excel 2007 but the file is saved as 97-2003. A2:K2 are merged, and when I manually hide columns in this range it works fine but when I run it through the code it hides column A through K. "Dave Peterson" wrote: First, nothing to do with your question... Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False could be replaced with: Me.Rows("6:17").EntireRow.Hidden = False Me.Columns("D:AB").EntireColumn.Hidden = False (And the same with the other .select/selection.entirecolumn... stuff, too.) There's not too many things that code needs to select first before working with them. Second, This is a guess... I'm guessing that you have merged cells in your worksheet. And you may find that hiding a cell that is merged with cells in columns that are hidden will hide all the columns in that merged area (this behavior changes with versions of excel, though). What version of excel has the problem for you. Ayo wrote: I have this Worksheet event that I am trying to use to hide rows and columns based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding Rows and Columns in code
Thanks Dave. I figured out a thrid alternative; copy the sheet, Delete
shift:=Left all the cells that I needed to delete and everything works great now. Just the way I wanted it to. Thanks for all the insight, really appreciated. "Dave Peterson" wrote: And if you unmerge those cells, does your code work ok? If it does, I think you have two choices. Live with the merged cells and the code not working the way you want or remove the merged cells permanently and have the code work the way you want. Ayo wrote: I am using Excel 2007 but the file is saved as 97-2003. A2:K2 are merged, and when I manually hide columns in this range it works fine but when I run it through the code it hides column A through K. "Dave Peterson" wrote: First, nothing to do with your question... Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False could be replaced with: Me.Rows("6:17").EntireRow.Hidden = False Me.Columns("D:AB").EntireColumn.Hidden = False (And the same with the other .select/selection.entirecolumn... stuff, too.) There's not too many things that code needs to select first before working with them. Second, This is a guess... I'm guessing that you have merged cells in your worksheet. And you may find that hiding a cell that is merged with cells in columns that are hidden will hide all the columns in that merged area (this behavior changes with versions of excel, though). What version of excel has the problem for you. Ayo wrote: I have this Worksheet event that I am trying to use to hide rows and columns based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hiding Rows and Columns in code
Glad you found something that worked for you.
Ayo wrote: Thanks Dave. I figured out a thrid alternative; copy the sheet, Delete shift:=Left all the cells that I needed to delete and everything works great now. Just the way I wanted it to. Thanks for all the insight, really appreciated. "Dave Peterson" wrote: And if you unmerge those cells, does your code work ok? If it does, I think you have two choices. Live with the merged cells and the code not working the way you want or remove the merged cells permanently and have the code work the way you want. Ayo wrote: I am using Excel 2007 but the file is saved as 97-2003. A2:K2 are merged, and when I manually hide columns in this range it works fine but when I run it through the code it hides column A through K. "Dave Peterson" wrote: First, nothing to do with your question... Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False could be replaced with: Me.Rows("6:17").EntireRow.Hidden = False Me.Columns("D:AB").EntireColumn.Hidden = False (And the same with the other .select/selection.entirecolumn... stuff, too.) There's not too many things that code needs to select first before working with them. Second, This is a guess... I'm guessing that you have merged cells in your worksheet. And you may find that hiding a cell that is merged with cells in columns that are hidden will hide all the columns in that merged area (this behavior changes with versions of excel, though). What version of excel has the problem for you. Ayo wrote: I have this Worksheet event that I am trying to use to hide rows and columns based on the value in Range("A2"). The code looks OK and it doesn't give any error except that it doesn't quite does want it is intended to do. For instance, when it is run, columns A:L are hidden everytime. This is not the intent for the code and I can't figure out why it is doing this. Any helpful insight will be greatly appreciated. The entire code follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Me.Rows("6:17").Select Selection.EntireRow.Hidden = False Me.Columns("D:AB").Select Selection.EntireColumn.Hidden = False If Target.Address() = "$A$2" Then Application.EnableEvents = False Select Case Target.Value Case "NLP1 Infill: Milestone Completion Counts by Market" Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select Selection.EntireColumn.Hidden = True Me.Range("7:7,12:12,15:15").Select Selection.EntireRow.Hidden = True Case "NLP2: Milestone Completion Counts by Market" Me.Range("E:K,N:N,W:W,Y:AA").Select Selection.EntireColumn.Hidden = True Me.Range("9:9,11:11,13:13").Select Selection.EntireRow.Hidden = True Case "NLP3: Milestone Completion Counts by Market" Me.Range("E:K,M:M,U:Z").Select Selection.EntireColumn.Hidden = True Me.Range("13:13").Select Selection.EntireRow.Hidden = True End Select End If Application.EnableEvents = True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hiding rows and columns in XL 07 | New Users to Excel | |||
| hiding rows or columns | Excel Programming | |||
| Hiding Rows and Columns | Excel Programming | |||
| Hiding rows and columns | Excel Programming | |||
| Code not hiding columns | Excel Programming | |||
Linear Mode
