A Better way of hiding colums based on cell values?

[JoeRooney]

Hi guys ,

I am sure there is a better way of hiding columns than the code below that i am using.

Basically I need to hide the columns based on the cell value of column and whatever row I am currently on and ignore the pervious cell value in column 20 value if that make sense.

The spreadsheet I am working on will be updated continuously.

The below code works fine but rather than me having to update continuously as new rows are added I am sure there is a better way of doing this.

Any help is greatly appreciated.

Thanks,


rivate Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 20 And Target.Row = 3 And Target.Value = "Yes" Then
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = False
End If

If Target.Column = 20 And Target.Row = 4 And Target.Value = "Yes" Then
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = False
End If


If Target.Column = 20 And Target.Row = 5 And Target.Value = "Yes" Then
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = False
End If

If Target.Column = 20 And Target.Row = 6 And Target.Value = "Yes" Then
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = False
End If

If Target.Column = 20 And Target.Row = 7 And Target.Value = "Yes" Then
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("U:AI").Select
Application.Selection.EntireColumn.Hidden = False
End If



End Sub

 

[dmt32]

Hi,
try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 20 And Target.Row > 2 Then
        Application.Columns("U:AI").EntireColumn.Hidden = CBool(Target.Value = "Yes")
    End If
End Sub

and see if this does what you want.


Dave

 

[JoeRooney]

Hi Dave,

Works perfect , thanks a million

Joe

 

[dmt32]

Hi Dave,

Works perfect , thanks a million

Joe

welcome - thanks for feedback

Dave