Excel Macro for multiple filter

0
votes

I am writing a excel macro which require to get the input from user from the particular column and then filter the value based on user input.

Note:

1) The input should prompt for list box that will show all the values present in that column. 2) User can select multiple value one or more. 3) Based on user input filter the sheet and display the result.

Experts in Macro can you please advise.

I tried since the input values from user is dynamic(one or more selection) I am facing the challenge.

Range("B2").AutoFilter Field:=2, Criteria1:=Array("Selection1","Selection2"), Operator:=xlFilterValues

2
vbaexcel
why don't you use excel UI built in filter features? (you should find them under "Data -> Sort and Filter" - user3598756
Hi, I want to do it using macro.. - user1954762
are you using Userforms? Give more details about your user interface - user3598756
yes I am using user forms but how to get the value once user click ok button and do the filer in excel column based on listbox selection in user form - user1954762

2 Answers

2
votes

Your Range needs to be for the entire table which you wish to filter and use the Field parameter to specify which column to filter. e.g. Range("A1:D50").AutoFilter Field:=2 will filter the B column for a table spanning from cells A1 to D50.

Set your filter values before the AutoFilter line, this will also make it easier for you to build your dynamic feature to specify the filter values. e.g.

filterValues = Array("Criteria1","Criteria2", "Criteria3")
Range("A1:D50").AutoFilter Field:=2, Criteria1:=filterValues, Operator:=xlFilterValues

In order to get input from a user to populate the filterValues array you will need to build a userform for this.

0
votes

In order to have more criteria in autofilter, you should give them separately. I have not seen excel taking it as an array. Separately like this:

Sub Makro2()

    ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=1, Criteria1:="123"
    ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:="122"

End Sub