Expert: Stuart Resnick - 11/20/2008

Question
I've been working on this for a few hours, and I seem to be running in circles.  I want to automatically change a table of values based on what sorting criteria are used in a dropdown menu.  Basically- I have student's names in Column A, and various tests and quizzes in Columns B through L.  I have a dropdown menu (named as "Sort") that also has the list of all tests and quizzes given.  I want to be able to just click "Test 4" from the dropdown menu and have every row without a score in it in the column containing "Test 4"'s scores to be hidden.  But then I also want to be able to pick "Quiz 1" from the dropdown menu and not have to un-hide all previously hidden rows from "Test 4".  This is sounding confusing, but I know it must be something simple I'm missing.  Here's the code I've put together so far.  Any help you can give me is greatly appreciated.


' Using worksheet_change so that whenever I change the dropdown menu cell it will run this macro

Private Sub worksheet_change(ByVal target As Range)
Dim myRg As Range

' "Sort" is what I used for the name of the dropdown menu cell
If Not Intersect(target, Me.Range("Sort")) Is Nothing Then

If Range("Sort").Value = "All Names" Then
   Range("A5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Test 1" Then
   Range("B5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Test 2" Then
   Range("C5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Test 3" Then
   Range("D5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Test 4" Then
   Range("E5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Quiz 1" Then
   Range("F5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Quiz 2" Then

   Range("G5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Quiz 3" Then

   Range("H5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
If Range("Sort").Value = "Quiz 4" Then

   Range("L5").Select
   Selection.AutoFilter Field:=1, Criteria1:="<>"
   
End If
End If
End Sub

Answer
Private Sub worksheet_change(ByVal target As Range)
   Dim sortBy As String
   If Not Intersect(target, Me.Range("Sort")) Is Nothing Then
       sortBy = target.Value
       Me.AutoFilterMode = False
       If sortBy = "All Names" Then
           Range("A5").AutoFilter Field:=1, Criteria1:="<>"
       ElseIf sortBy = "Test 1" Then
           Range("B5").AutoFilter Field:=2, Criteria1:="<>"
       ElseIf sortBy = "Test 2" Then
           Range("C5").AutoFilter Field:=3, Criteria1:="<>"
'etc etc for other sort options...
       End If
   End If
End Sub