Format column auto. to "hidden"
 

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

Format column auto. to "hidden"
 

Hi Nils
Paste the code below into the sheet code (right-click on your sheet tab and
select View code)
This will hide column B whenever any value (text or number) is entered into
J5 and unhide column B whenever J5 is empty
To hide rows change Columns to Rows and letters to numbers
ie Rows("1:1").EntireRow.hidden = True
Sample code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$5" Then
If Target.Value < "" Then
Columns("B:B").EntireColumn.Hidden = True
Else: Columns("B:B").EntireColumn.Hidden = False
End If
End If
End Sub

Regards
Pascal

a écrit dans le message de
...
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