Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
I have a couple of worksheets in the same workbook who needs to show
columns R:T and row 15 if the cell AE1 changes in 1 or 2 triggerd by a formula by a dropdown box in the mainsheet (input). The following code doesn't work. Why?? Sub DropDown_Change() 'hide or unhdide columns and row Dim sh As Worksheet For Each sh In Worksheets If sh.Name < "input" Then If sh.Range("Ae1").Value = 2 Then Columns("P:R").Select Selection.EntireColumn.Hidden = True Rows("15:15").Select Selection.EntireRow.Hidden = True ElseIf sh.Range("Ae1").Value = 1 Then Rows("15:15").Select Selection.EntireRow.Hidden = False Columns("p:r").Select Selection.EntireColumn.Hidden = False End If End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
Try this:-
Sub DropDown_Change() 'hide or unhdide columns and row Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Select If ws.Name < "input" Then If ws.Range("AE1").Value = 2 Then Columns("P:R").Select Selection.EntireColumn.Hidden = True Rows("15:15").Select Selection.EntireRow.Hidden = True ElseIf ws.Range("AE1").Value = 1 Then Rows("15:15").Select Selection.EntireRow.Hidden = False Columns("p:r").Select Selection.EntireColumn.Hidden = False End If End If Next End Sub Does that work? Mike "A. Karatas" wrote: I have a couple of worksheets in the same workbook who needs to show columns R:T and row 15 if the cell AE1 changes in 1 or 2 triggerd by a formula by a dropdown box in the mainsheet (input). The following code doesn't work. Why?? Sub DropDown_Change() 'hide or unhdide columns and row Dim sh As Worksheet For Each sh In Worksheets If sh.Name < "input" Then If sh.Range("Ae1").Value = 2 Then Columns("P:R").Select Selection.EntireColumn.Hidden = True Rows("15:15").Select Selection.EntireRow.Hidden = True ElseIf sh.Range("Ae1").Value = 1 Then Rows("15:15").Select Selection.EntireRow.Hidden = False Columns("p:r").Select Selection.EntireColumn.Hidden = False End If End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
it debugs on the line
ws.select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
It doesn't on my machine. Have you copied all my code because I made a few
changes to yours. Mike "A. Karatas" wrote: it debugs on the line ws.select |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
It really debugs. No matter what I do. I copied the macro into a
module. If I take out the line the macro only works in the sheet "input' |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row & columns with particular cell value
Hi,
I'm afraid I'm at a complete loss to understand why it doesn't work, it works perfectly on a second machine i've tried. Mike "A. Karatas" wrote: It really debugs. No matter what I do. I copied the macro into a module. If I take out the line the macro only works in the sheet "input' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Columns based on a cell value | Excel Worksheet Functions | |||
Hide columns if specific cell is blank? | Excel Discussion (Misc queries) | |||
Hide or Unhide certain columns based on a cell value | Excel Programming | |||
Hide all columns that span a merged cell? | Excel Programming | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions |