Excel Macro for multiple filter

Question

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

why don't you use excel UI built in filter features? (you should find them under "Data -> Sort and Filter" — user3598756
– user3598756, Commented Jul 27, 2016 at 7:53
are you using Userforms? Give more details about your user interface — user3598756
– user3598756, Commented Jul 27, 2016 at 9:17
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
– user1954762, Commented Jul 27, 2016 at 9:45

Carrosive · Accepted Answer · 2016-07-27 09:23:48Z

2

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.

answered Jul 27, 2016 at 9:23

Carrosive

9092 gold badges10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2016-07-27 07:46:07Z

0

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

answered Jul 27, 2016 at 7:46

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

3 Comments

user1954762 Over a year ago

it will take it in array.. I tested it's working... but I want if there is more than one .. where we user input count is dynamic what to be done for that case.

Vityata Over a year ago

So, if your question is how to take the user input dynamically, then the answer is - use events. Like this:cpearson.com/excel/Events.aspx

user1954762 Over a year ago

this site you had provided doesn't solve my purpose. anyways thanks!

Collectives™ on Stack Overflow

Excel Macro for multiple filter

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related