Macro Enabled Column Hiding based on data validated drop down

[JB794]

Hi everyone-

I'm trying to get help writing a macro for hiding columns based on a drop down answer. The drop down would be in cell B7 on Sheet 1. The colums affected would be in each worksheet in the workbook. The data validation would have a drop down selection choice of 1 through 16. The columns affected range would be H through W. For example, in the drop down if 1 was selected, only column H would show. If two, H and I would show...etc. This macro should affect all worksheets in the workbook EXCEPT sheet 1.


This is very time sensitive, i'm hoping someone will provide an answer soon!

Thanks all

 

[scott_n_phnx]

Here is one, that with some modification, should get you what you need.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D7")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
If Target.Value = "O" Then
    Columns("O:W").Select
    Selection.EntireColumn.Hidden = False
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = True
End If

If Target.Value = "P" Then
    Columns("O:W").Select
    Selection.EntireColumn.Hidden = False
    Columns("P:P").Select
    Selection.EntireColumn.Hidden = True
End If

If Target.Value = "Q" Then
    Columns("O:W").Select
    Selection.EntireColumn.Hidden = False
    Columns("Q:Q").Select
    Selection.EntireColumn.Hidden = True
End If

If Target.Value = "R" Then
    Columns("O:W").Select
    Selection.EntireColumn.Hidden = False
    Columns("R:R").Select
    Selection.EntireColumn.Hidden = True
End If
Application.ScreenUpdating = True
End Sub

 

[rcirone]

Here is another to hide the columns

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B7").Value = "1" Then
        Range("H:H").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "2" Then
        Range("I:I").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "3" Then
        Range("j:j").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "4" Then
        Range("k:k").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "5" Then
        Range("l:l").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "6" Then
        Range("m:m").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "7" Then
        Range("n:n").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "8" Then
        Range("o:o").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "9" Then
        Range("p:p").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "10" Then
        Range("q:q").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "11" Then
        Range("r:r").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "12" Then
        Range("s:s").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "13" Then
        Range("t:t").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "14" Then
        Range("u:u").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "15" Then
        Range("v:v").EntireColumn.Hidden = True
    ElseIf Range("B7").Value = "16" Then
        Range("w:w").EntireColumn.Hidden = True
    

    End If
End Sub