Hide/Unhide Columns using VBA

[hq_life]

Hello All,

First post. New to VBA so really confused on what to do next.

What I am trying to achieve:
In Sheet 1, I have a drop down from "1 to 11"
If someone selects 1, I want to hide Columns D:M in Sheet 13. If someone selects 2, I want to hide columns E:M in Sheet 13, and so on.

This is the code I have so far:

If [B9] = "1" Then
Sheet13.Columns("D:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("D:M").EntireColumn.Hidden = False
End If

If [B9] = "2" Then
Sheet13.Columns("E:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("E:M").EntireColumn.Hidden = False
End If

This code works if I select "2", but when I select "1" it ONLY hides column D, and not the remaining E:M. I also want to extend this code if I select 3 (Hide F:M), 4 (Hide G:M), etc...

 

[Joe4]

Welcome to the Board!

I think you have conflicting information.
For example, if you choose 2, the "Else" portion of the first IF is telling it to NOT hide columns D:M, but the second IF is telling hide columns E:M.

In these situations, I think it is best to usually start of unhiding everything, and then just hiding what you need.
And I would recommend using a "Case" statement insteaqd of a whole bunch of IF/THEN/ELSE statements, i.e.

Sheet13.Columns("D:M").EntireColumn.Hidden = False

Select Case Range("B9")
    Case 1
        Sheet13.Columns("D:M").EntireColumn.Hidden = True
    Case 2
        Sheet13.Columns("E:M").EntireColumn.Hidden = True
End Select

 

[Micron]

Each If block will run because you don't Exit Sub when the block is executed. Try adding that before each End If. However, it's not clear what you want to happen if I first choose 1, then I choose 2. What's supposed to happen to the hidden columns? If unhide, then probably best to first unhide all then hide according to the choice.
Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[Micron]

Late again. But the unhide would be D:M not E:M?

 

[Joe4]

Late again. But the unhide would be D:M not E:M?

Good catch.
I went back and updated the first line of the VBA code in my last reply.

 

[Herakles]

Hello All,

First post. New to VBA so really confused on what to do next.

What I am trying to achieve:
In Sheet 1, I have a drop down from "1 to 11"
If someone selects 1, I want to hide Columns D:M in Sheet 13. If someone selects 2, I want to hide columns E:M in Sheet 13, and so on.

This is the code I have so far:

If [B9] = "1" Then
Sheet13.Columns("D:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("D:M").EntireColumn.Hidden = False
End If

If [B9] = "2" Then
Sheet13.Columns("E:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("E:M").EntireColumn.Hidden = False
End If

This code works if I select "2", but when I select "1" it ONLY hides column D, and not the remaining E:M. I also want to extend this code if I select 3 (Hide F:M), 4 (Hide G:M), etc...

Welcome to Mr Excel.

Does this give you what you need?

Put this code into the worksheet code module of the worksheet with the drop down validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrColumns() As String
Dim Ws As Worksheet

    If Target.CountLarge > 1 Then
        Exit Sub
    End If

    Set Ws = Worksheets("Sheet13")
    
    arrColumns = Split("D:M,E:M,F:M,G:M,H:M,I:M,J:M,K:M,L:M,M:M", ",")
        
    If Not Intersect(Target, Range("B9")) Is Nothing Then

        Ws.Range("D:M").EntireColumn.Hidden = False
        
        Ws.Range(arrColumns(Target.Value - 1)).EntireColumn.Hidden = True
    
    End If

End Sub

 

[hq_life]

Sorry - I am very new to VBA.
Would this code be in addition to the IF statement? Something like the below? Very sorry!

If [B9] = "1" Then
Sheet13.Columns("D:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("D:M").EntireColumn.Hidden = False
Select Case Range("B9")
    Case 1
        Sheet13.Columns("D:M").EntireColumn.Hidden = True
    Case 2
        Sheet13.Columns("E:M").EntireColumn.Hidden = True
End Select
End If

 

[Herakles]

Sorry - I am very new to VBA.
Would this code be in addition to the IF statement? Something like the below? Very sorry!

If [B9] = "1" Then
Sheet13.Columns("D:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("D:M").EntireColumn.Hidden = False
Select Case Range("B9")
    Case 1
        Sheet13.Columns("D:M").EntireColumn.Hidden = True
    Case 2
        Sheet13.Columns("E:M").EntireColumn.Hidden = True
End Select
End If

I'm not sure where your code is.

This takes the place of any other code you have to hide and hide and unhide these columns.

It is automaticlly triggered when the value in B9 changes when a different value is selected from the drop down list.

 

[Joe4]

Sorry - I am very new to VBA.
Would this code be in addition to the IF statement? Something like the below? Very sorry!

If [B9] = "1" Then
Sheet13.Columns("D:M").EntireColumn.Hidden = True
Else
Sheet13.Columns("D:M").EntireColumn.Hidden = False
Select Case Range("B9")
    Case 1
        Sheet13.Columns("D:M").EntireColumn.Hidden = True
    Case 2
        Sheet13.Columns("E:M").EntireColumn.Hidden = True
End Select
End If

No, my code replaces ALL of that.

Note that if the value in cell B9 is a valid numeric entry and not text, you do not need the double-quotes around it (they are used for text entries, not numeric ones).
That is why I used:

Case 1

instead of

Case "1"