VBA - How to create buttons/dropdowns that filter data in rows

[LNG2013]

Hello!

In VBA how can I make some dropdown selectors to help filter data sets? That can then be used in calculations.

The table is on sheet2, Column A is names, Column B is months. I want to give the user the ability to select a name from the choices in column A and then select the month for the data set in column B.

The big trick is that there might be several entries for the same person in the month. So it will need to select all data entries for "Bob" in "October." I could then have calculations for averaging all of the data sets in columns C fot the user and add all the data sets from column D together for the sum?

 

[shg]

One way:

Sub QuickAutofilter()
    With ActiveCell
        If Not .Worksheet.AutoFilterMode Then .AutoFilter
        .CurrentRegion.AutoFilter Field:=.Column - .CurrentRegion.Column + 1, _
                                  Criteria1:=.Value
    End With
End Sub

Assign a convenient shortcut. Select a cell in one column, run the code, select a cell in another column, run the code.

 

[LNG2013]

Hey shg, I am trying to set this up as an easy interface for other staff... and don't want them to manipulate the data directly.

What I am looking for I guess, is a script that will automatically select column A, turn on filtering, and in a dropdown in sheet1 show the results for them to select from. So let's say they select Bob in this dropdown, in the second dropdown there will be a list of months for Bob based on the data sets, the user can then select one of those months. Which then will further filter the data results.

 

[shg]

and in a dropdown in sheet1 show the results for them to select from. So let's say they select Bob in this dropdown, in the second dropdown there will be a list of months for Bob based on the data sets

With the exception of turning AutoFilter on to begin with, that's how AutoFilter works ...

 

[LNG2013]

Right but I want to put the results of the autofilter in a seperate dropdown that is on a seperate sheet.

With the exception of turning AutoFilter on to begin with, that's how AutoFilter works ...