I am trying to make certain columns disappear if they are not needed. The rule is if nothing is in the column that triggers the others to show up or if they say "no" then the columns are hidden. If the trigger column says "yes" then they hidden columns appear.
Case 11 works fine but case 14 and 22 do not for some reason. Any thoughts?
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("K:K,O:O,W:W"), Target) Is Nothing Then Exit Sub
Select Case Target.Column
Case 11
If WorksheetFunction.CountIf(Range("K2:k466"), "Yes") >= 1 Then
Range("L:N").EntireColumn.Hidden = False
Else
Range("L:N").EntireColumn.Hidden = True
End If
Case 14
If WorksheetFunction.CountIf(Range("O2:O466"), "Yes") >= 1 Then
Range("P:S").EntireColumn.Hidden = False
Else
Range("P:S").EntireColumn.Hidden = True
End If
Case 22
If WorksheetFunction.Count(Range("W2:W466")) >= 1 Then
Range("U:V,X:AD").EntireColumn.Hidden = False
Else
Range("U:V,X:AD").EntireColumn.Hidden = True
End If
End Select
End Sub
Bookmarks