Hide Multiple Columns with Multiple potential inputs

[marcusja2002]

I want to hide multiple columns depending on the input in cell A1. It can be Anything from Jan-Dec. Once it checks the contents I need to hide multiple columns that will change depending on the cell.

This is what I have so far;


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = "January" Then
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
Columns("Q").EntireColumn.Hidden = True
Columns("R").EntireColumn.Hidden = True
Columns("S").EntireColumn.Hidden = True
Columns("T").EntireColumn.Hidden = True
Columns("U").EntireColumn.Hidden = True
Else
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = False
Columns("P").EntireColumn.Hidden = False
Columns("Q").EntireColumn.Hidden = False
Columns("R").EntireColumn.Hidden = False
Columns("S").EntireColumn.Hidden = False
Columns("T").EntireColumn.Hidden = False
Columns("U").EntireColumn.Hidden = False
End If
End Sub


Works great for January, but I can't figure out how to add the other 11 months.

Thanks in advance for your help.

 

[JustynaMK]

Hi marcusja2002,

Please try the following:

Select Case Range("A1").Value
Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
Range("K:U").EntireColumn.Hidden = True
Case Else
Range("K:U").EntireColumn.Hidden = False
End Select

 

[marcusja2002]

Hi marcusja2002,

Please try the following:

Select Case Range("A1").Value
Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
Range("K:U").EntireColumn.Hidden = True
Case Else
Range("K:U").EntireColumn.Hidden = False
End Select

I placed this into my sheet and nothing happened.

Another way to say what I'm looking for is this. When Cell A1 = January I want k-U hidden(only see J), when its February I want j and l-m hidden (only see K). So on for the rest of the year.

 

[JustynaMK]

Ahh sorry for misunderstanding. Try the following:

Sub Months()Dim i As Integer


Select Case Range("A1").Value
Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
    Range("[B]A3[/B]").Formula = "=MONTH(A1&""-1""&0)"
    i = Range("[B]A3[/B]").Value
    Range("J:U").EntireColumn.Hidden = True
    Columns(i + 9).EntireColumn.Hidden = False
    Range("[B]A3[/B]").ClearContents
Case Else
    Range("J:U").EntireColumn.Hidden = False
End Select


End Sub

Please note that the code creates a temporary Excel function in cell A3. If you have anything significant there simply modify the range value (highlighted in the code above).

I hope it helps.

 

[marcusja2002]

Ahh sorry for misunderstanding. Try the following:

Sub Months()Dim i As Integer


Select Case Range("A1").Value
Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
    Range("[B]A3[/B]").Formula = "=MONTH(A1&""-1""&0)"
    i = Range("[B]A3[/B]").Value
    Range("J:U").EntireColumn.Hidden = True
    Columns(i + 9).EntireColumn.Hidden = False
    Range("[B]A3[/B]").ClearContents
Case Else
    Range("J:U").EntireColumn.Hidden = False
End Select


End Sub

Please note that the code creates a temporary Excel function in cell A3. If you have anything significant there simply modify the range value (highlighted in the code above).

I hope it helps.

Thanks this works great Thank you. What can I change so it automatically runs every time the cell is changed. I have to press the play button to activate it in the current state.

 

[JustynaMK]

Try pasting the following code into the Sheet1 code view:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer


If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
    Select Case Range("A1").Value
    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
        Range("A3").Formula = "=MONTH(A1&""-1""&0)"
        i = Range("A3").Value
        Range("J:U").EntireColumn.Hidden = True
        Columns(i + 9).EntireColumn.Hidden = False
        Range("A3").ClearContents
    Case Else
        Range("J:U").EntireColumn.Hidden = False
    End Select
End If


End Sub

 

[marcusja2002]

Try pasting the following code into the Sheet1 code view:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer


If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
    Select Case Range("A1").Value
    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
        Range("A3").Formula = "=MONTH(A1&""-1""&0)"
        i = Range("A3").Value
        Range("J:U").EntireColumn.Hidden = True
        Columns(i + 9).EntireColumn.Hidden = False
        Range("A3").ClearContents
    Case Else
        Range("J:U").EntireColumn.Hidden = False
    End Select
End If


End Sub

works perfectly...thank you so much.

 

[marcusja2002]

This above formula has been working great for months of the year. Now I want to utilize if for a more unique set of data. I have multiple company names. I want to hide a range of columns depending on the company chosen. This is to help direct a specific pull down list since I'm not 100% sure how else to program a pull down list that changes depending on the company chosen.

Thanks in advance.