Filtering by Selecting from Drop down menu.

Hi,

I am in need of expert help and as I was browsing the nest I came across this site, so I joined.

Well I have created a work book which pulls out data from the schools database and populate separate worksheets.

Now I have field titles such us 'Tutor Group', 'Gender', 'Ethnicity' etc. Instead of using the filtering function on excel, I have drop down menus at the top of the page which I want to use to filter through the page. So what I need is a script to help me so that when I select an attribute from the various drop downs the sheet will be sorted to give me data relating to the attribute selected and hide all other rows.

Example of Table is below.

So for example when I select 'AB' from the TG drop down it will hide rows 2 and 4 and leave out 1 and 3. Now having just rows 1 and 3 I should be able to also select 'WENG' from the Ethnicity drop down and it will hide row 1 and only have row 3 showing.

I hope this is clear. Please remember I am using drop down menu as in List from validation function and not a combo box.

Thanks in advance for your expert help.

Isn't dropdowns exactly what filter does?

Maybe this utility by Roger Govier will help you, http://www.contextures.com/excelfilesRoger.html, scroll down to find the utility called FastFilter.

Many thanks, i will have a look and let you know if i have any problems.

Thanks Bob.

Can you or anyone else help me again please?

With the filtering of data what I realised is that I am unable to do a multiple selection. What I mean here is that, when I select AB as for the TG for example, it will filter and hide the rows that have other TGs. Now when I go to select M for the Gender it will then hide the one row with F in the gender and show me all the rows with M.

What I want to happen is when I filter to AB for the TG I want it to hide all the other TGs and then with the data showing, I want to be able to filter to M for the gender and it will filter according the data showing.

Step by step as to what I want.

This is an example of what is happening and after I will demonstrate what I want to see

Starting Point

Step one - Filter by TG to AB

Step two - Filter by Gender to M

However, this is rather what I want to see happening

Starting point

Step one - Filter by TG to AB

Step two - Filter by Gender to M 

So what I am asking is, is it possible to filter multiple times without the sheet resetting before it filters?

Hope this is clear...

Many Thanks in Advance

Below is the script I have written to run the filters. Or is there a way i can send the Work book on here?

'This will filter the table to the attribute in the TG drop down.
Sub TGFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("C4:C40")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("D1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will filter the table to the attribute in the Gender drop down.
Sub GenderFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("D4:D40")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("F1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will filter the table to the attribute in the Ethnicity drop down.
Sub EthnicityFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("E4:E400")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("I1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will unhide all rows
Sub Reset()

 Rows("4:9").Select
    Selection.EntireRow.Hidden = False
    Rows("1").Select
End Sub

Here is the script I have

'This will filter the table to the attribute in the TG drop down.
Sub TGFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("C4:C40")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("D1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will filter the table to the attribute in the Gender drop down.
Sub GenderFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("D4:D40")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("F1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will filter the table to the attribute in the Ethnicity drop down.
Sub EthnicityFilter()
   
    For i = 1 To 2
        Worksheets(i).EnableCalculation = False
    Next
   
    Dim rRange, TheRow As Range
    Set rRange = Worksheets("Test Sheet").Range("E4:E400")

    Dim iRow As Integer
     iRow = 1
           
   
           
    While rRange(iRow, 1).Value <> ""

        'rRange(iRow, 1).Select

        If rRange(iRow, 1).Value = Worksheets("Test Sheet").Range("I1").Value Then
           rRange(iRow, 1).EntireRow.Hidden = False
        Else
           rRange(iRow, 1).EntireRow.Hidden = True
        End If

        iRow = iRow + 1
    Wend
   
    For i = 1 To 2
    Worksheets(i).EnableCalculation = True
    Next
   
End Sub

'This will unhide all rows
Sub Reset()

 Rows("4:9").Select
    Selection.EntireRow.Hidden = False
    Rows("1").Select
End Sub

Post the workbook with the code mate, a lot easier for us.

How do I do that, can you please talk me through it.