Autofilter macro for noobie

[Guest]

I am trying to set up a macro that will initiate a pop up box that has all of
my column headers in it. Then I can go through the list and select which
filters I use on any or all of the columns. Once I pick all the criteria I
want the macro runs and returns my data for all rows that match the filters.
Essetially its the same as going through column by column and selecting
different filters until I get down to a small list.

eg.
column 1 (Name), Column 2 (State), Column 3 (vehicle type), Column 4
(vehicle make) ...

The popup would prompt me to select filters and I could pick Truck (col 3)
and Toyota (col 4). Or choose state and toyota (Etc etc etc). Then have the
correct autofilters automatically run.

Thanks in advance for any suggestions.

 

[Tom Ogilvy]

It could probably be done, but you are duplicating what is already done by
the built in dropdowns. It would also take a lot of code if you want to
reproduce their ability to be progressively restrictive. Also, the custom
feature of the builtin filter offers a lot more flexibility - again, that
would tak a lot of work to duplicate. Where is the advantage to having a
popup?

 

[Guest]

My thought is that a popup would be a lot easier to filter down the
information for a very large data set (20+ columns) that contains inventory
information. If a customer calls in and needs specific things I need to find
the inventory items that are the closest fit. I thought a popup that asks
for the customer's criteria would be much faster than going through 20 or
more columns and filtering on each one individually. Especially if the
customer is on the phone waiting to find out if I have anything. However, I
have not done much work with macros, so I am not sure if its even possible.

mnvkngs

 

[Guest]

I figured it out myself. It may not be the most efficient code, but it
works. Where sheet1 (B1-B7) containa my filter values with * used for
wildcards.

Sub Filter()
Dim WSCode1 As String
WSCode1 = Worksheets("Sheet1").Cells(1, 2).Value
Selection.AutoFilter Field:=1, Criteria1:=WSCode1

Dim WSCode2 As String
WSCode2 = Worksheets("Sheet1").Cells(2, 2).Value
Selection.AutoFilter Field:=2, Criteria1:=WSCode2

Dim WSCode3 As String
WSCode3 = Worksheets("Sheet1").Cells(3, 2).Value
Selection.AutoFilter Field:=3, Criteria1:=WSCode3

Dim WSCode4 As String
WSCode4 = Worksheets("Sheet1").Cells(4, 2).Value
Selection.AutoFilter Field:=4, Criteria1:=WSCode4

Dim WSCode5 As String
WSCode5 = Worksheets("Sheet1").Cells(5, 2).Value
Selection.AutoFilter Field:=5, Criteria1:=WSCode5

Dim WSCode6 As String
WSCode6 = Worksheets("Sheet1").Cells(6, 2).Value
Selection.AutoFilter Field:=6, Criteria1:=WSCode6

Dim WSCode7 As String
WSCode7 = Worksheets("Sheet1").Cells(7, 2).Value
Selection.AutoFilter Field:=7, Criteria1:=WSCode7
End Sub