Hide Columns based on Value

[jptes215]

So I'm pretty new to VBA and I'm not entirely sure how to approach this. I'm working with an existing report and my boss doesn't want it transposed or changed from the current format. I have productivity metrics running down column B and C4:AO4 has the headers (names of different offices). I'm basically trying to simplify reading this huge report by only showing selected offices. I was thinking of creating a combo box that contains all the office names and then when selecting an office name it hides all other offices (or columns) and only shows the metrics of the selected office. I'm not sure where to start.

 

[BiocideJ]

In the sheet with the headers, create a cell with Data Validation type:= LIST and the Source:=$C$4:$AO$4 (in my example I used A1)
NOTE:This cell can be anywhere, but you probably shouldn't put it in columns C:AO since they will be hidden at various times.

Next, place the following code in the SHEET object of the Visual Basic project for the sheet where you want to perform this action.

 Private Sub Worksheet_Change(ByVal Target As Range)
'Change A1 in the row below to reference whichever cell contains the drop-down list
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        Range("C4:AO4").EntireColumn.Hidden = True
        If Range("C4:AO4").Find(Target.Value) Is Nothing Then
            Range("C4:AO4").EntireColumn.Hidden = False 'no selection (show all)
        Else
            Range("C4:AO4").Find(Target.Value).EntireColumn.Hidden = False
        End If
    End If
End Sub