excel column format to "hidden"

  • Thread starter Thread starter Nils
  • Start date Start date
N

Nils

How do I set up an automatic column format to "zero" width
or hidden. What I want to do is when a particular cell
has a certain value I want another column or row to hide
itself and when the cell is changed to another value the
column will show up again. I have looking through various
excel developer books and cannot seem to find the answer.
I even tried a macro and still could not get it to work.

In other words, if cell j5 = x (text) or a number - lets
say 5 then column "b" goes to hidden or zero width ---
then when cell j5 is not the x or 5 column "b" goes back
to normal and is seen!

P.S. same for rows as well!

I hope you can help!
Thanks, Nils
 
Nils,

Private Sub Worksheet_Change(ByVal Target As Range)
Set TestCell = Range("J5")
TestValue = "stuff"
If TestCell = TestValue Then
Range("B1").EntireColumn.Hidden = True
Else
Range("B1").EntireColumn.Hidden = False
End If
End Sub
 
I used this and it is excellent for rows and colums - one
last question.

How do I mix it and use at the same time with colums and
rows.

Thanks, Nils!
 
One way:

Private Sub Worksheet_Change(ByVal Target As Range)
Const cTestValue = "Stuff"
With Target(1)
If .Address(False, False) = "J5" Then
Columns("B").Hidden = (.Value = cTestValue)
Rows("6:10").Hidden = (.Value = cTestValue)
End If
End With
End Sub
 
Back
Top