Code to find value based on dropdown selection

[bemisdt]

Hope someone can help...

I have a dropdown list of employee names to filter a matrix-style spreadsheet. On selection, all columns except the selected are hidden.

My problem is that every time I add a new employee, I have to modify the code to match the name to a specific column. I need code that will search the columns in a range for the value from the dropdown, then hide all other columns. Here is a shot of my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("$E$2").Value
Case Is = "ALL OPERATORS"
Columns("F:AW").EntireColumn.Hidden = False
Range("Table12LL").Select
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Range("A1").Select
Case Is = "Al Castro"
Columns("F:F").EntireColumn.Hidden = False
Columns("G:AW").EntireColumn.Hidden = True
Case Is = "Austin Furrow"
Columns("G:G").EntireColumn.Hidden = False
Columns("F:F").EntireColumn.Hidden = True
Columns("H:AW").EntireColumn.Hidden = True
Case Is = "Bobby Dykman"

 

[WarPigl3t]

Assuming that your employee names are located in row 1 and that the first employee name starts at F1.

Sub mySelection()
     myDropDownListValue = Range("E2").Value
     firstColumn = 6
     lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
     Columns(firstColumn & ":" & lastColumn).Hidden = False
     If myDropDownListValue = "ALL OPERATORS" Then
          Exit Sub
     End If
     c = firstColumn
     Do Until c > lastColumn
          myValue = Cells(1, c).Value
          If myValue = myDropDownListValue Then
               Exit Do
          End If
          c = c + 1
     Loop
     If c > lastColumn Then
          Exit Sub
     End If
     dontHideThisColumn = c
     c = firstColumn
     Do Until c > lastColumn
          If c <> dontHideThisColumn Then
               Columns(c).Hidden = True
          End If
          c = c + 1
     Loop
End Sub

 

[bemisdt]

Thanks for the response, however nothing happens when I select from the dropdown. The names begin in F9, so I changed the 1 to 9 here... [myValue = Cells(9, c).Value] assuming it was referencing the row. Any ideas?

I wanted to send the file, but I'm sorry, I don't know how on this site.

Thanks for the help

 

[WarPigl3t]

Sub mySelection()
     myDropDownListValue = Range("E2").Value
     firstColumn = 6
     lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
     firstColumnLetter = ColumnLetter(firstColumn)
     lastColumnLetter = ColumnLetter(lastColumn)
     Columns(firstColumnLetter & ":" & lastColumnLetter).Hidden = False
     If myDropDownListValue = "ALL OPERATORS" Then
          Exit Sub
     End If
     c = firstColumn
     Do Until c > lastColumn
          myValue = Cells(9, c).Value
          If myValue = myDropDownListValue Then
               Exit Do
          End If
          c = c + 1
     Loop
     If c > lastColumn Then
          Exit Sub
     End If
     dontHideThisColumn = c
     c = firstColumn
     Do Until c > lastColumn
          If c <> dontHideThisColumn Then
               Columns(c).Hidden = True
          End If
          c = c + 1
     Loop
End Sub

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function