Hide row & columns with particular cell value

  • Thread starter Thread starter A. Karatas
  • Start date Start date
A

A. Karatas

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
 
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
 
It doesn't on my machine. Have you copied all my code because I made a few
changes to yours.

Mike
 
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'
 
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
 
Back
Top