Dynamic Hide & Unhide column

[frozenfiree88]

Hello,

So I am new to VBA coding.

I wanted to dynamically unhide and hide the column based on the value in my cell C3. My problem is the first 1 is working, but when i choose another category it will hide all or other columns that I don't want to hide.

If I type TestA I wanted to hide my column Y to AB to only show my column X.

Then if I wanted to type testB I wanted to show column Y but hide column X and Z to AB.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C3"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "testA": Columns("Y:AB").EntireColumn.Hidden = True
                            Columns("A:V").EntireColumn.Hidden = False
                            
        Case Is = "testB": Columns("Z:AB").EntireColumn.Hidden = True
                            Columns("X").EntireColumn.Hidden = True
                           Columns("A:V").EntireColumn.Hidden = False
                          
        Case Is = "testC": Columns("X:Y").EntireColumn.Hidden = True
                            Columns("AA:AB").EntireColumn.Hidden = True
                           Columns("A:V").EntireColumn.Hidden = False
                          
        Case Is = "testD": Columns("X:Z").EntireColumn.Hidden = True
                             Columns("AB").EntireColumn.Hidden = True
                           Columns("A:V").EntireColumn.Hidden = False
                          
        Case Is = "testE": Columns("X:AA").EntireColumn.Hidden = True
                           Columns("A:V").EntireColumn.Hidden = False

        End Select
End If
End Sub

Thank you in advanced!

 

[Kaper]

Try to always unhide all first and then hide required only, like (I changed the flocation of test cell for tests to not hide itself):

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("AC3"), Range(Target.Address)) Is Nothing Then
   Columns("A:AB").EntireColumn.Hidden = False
        Select Case Target.Value
        Case Is = "testA": Columns("Y:AB").EntireColumn.Hidden = True
                            
        Case Is = "testB": Columns("Z:AB").EntireColumn.Hidden = True
                            Columns("X").EntireColumn.Hidden = True
                          
        Case Is = "testC": Columns("X:Y").EntireColumn.Hidden = True
                            Columns("AA:AB").EntireColumn.Hidden = True
                          
        Case Is = "testD": Columns("X:Z").EntireColumn.Hidden = True
                             Columns("AB").EntireColumn.Hidden = True
                          
        Case Is = "testE": Columns("X:AA").EntireColumn.Hidden = True

        End Select
End If
End Sub

 

[frozenfiree88]

Try to always unhide all first and then hide required only, like (I changed the flocation of test cell for tests to not hide itself):

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("AC3"), Range(Target.Address)) Is Nothing Then
   Columns("A:AB").EntireColumn.Hidden = False
        Select Case Target.Value
        Case Is = "testA": Columns("Y:AB").EntireColumn.Hidden = True
                           
        Case Is = "testB": Columns("Z:AB").EntireColumn.Hidden = True
                            Columns("X").EntireColumn.Hidden = True
                         
        Case Is = "testC": Columns("X:Y").EntireColumn.Hidden = True
                            Columns("AA:AB").EntireColumn.Hidden = True
                         
        Case Is = "testD": Columns("X:Z").EntireColumn.Hidden = True
                             Columns("AB").EntireColumn.Hidden = True
                         
        Case Is = "testE": Columns("X:AA").EntireColumn.Hidden = True

        End Select
End If
End Sub

Hello Kaper,

I marked your reply as solution. This works really well.

Appreciate the quick solution.

Thanks

 

[Kaper]

Glad we could help and thanks for appreciation!