Hide/Unhide Columns

Hi,
I have sheet1 with names of staff and sheet2 with linkformulas to sheet1.
I want to hide or unhide columns in sheet2 wether there is values printed in the first sheet or not.

I assume there has to some ChangeVal code, but I can't solve this.

Any ideas?

//Thomas

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) Then
Sheet2.Range("A:A").EntireColumn.Hidden = True
Else
Sheet2.Range("A:A").EntireColumn.Hidden = False
End If
End If

End Sub


When you enter a value in cell A1 in sheet1, then the column A in sheet2 is not hidden, otherwise it is hidden. The above code is to be entered in the module for sheet1

Mangesh

Hi Mangesh

Thanks for your reply.

In your case there is only the TargetAdress is in range A1, what if I need the code to work in a range as A1:A30 in sheet1 and in A:M in sheet2

I tried to change your code as this(Blad is the name for sheet):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("Blad1!A1:A33") Then
If IsEmpty(Target.Value) Then
Blad2.Range("A:M").EntireColumn.Hidden = True
Else
Blad2.Range("A:M").EntireColumn.Hidden = False
End If
End If

End Sub

But it doesn't work

/Thomas

This code should go in the module for sheet Blad:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.column = 1 Then
If IsEmpty(Target.Value) Then
Worksheets("Blad2").Range("A:M").EntireColumn.Hidden = True
Else
Worksheets("Blad2").Range("A:M").EntireColumn.Hidden = False
End If
End If

End Sub


Mangesh