Apply multiple filters based on user input (VBA)

Question

I want to ask the user:

Which field/column to apply filters to?
How many filters are to be applied?

I want to take those n filters as input and apply them to the field of that column.

Refer these images: Before applying filters, After applying filters to Column A

Code:

Sub MultiFilter()
Dim colNumber As Integer, numberOfFilters As Integer
Dim filters(10) As String

'Column number to apply filters to
colNumber = InputBox("Enter column number to apply filter to (Column A = 1, B = 2, etc.)")

'Number of filters to apply
numberOfFilters = InputBox("Number of filters to apply to Column " & colNumber)

'Take multiple filters as input
For i = 0 To numberOfFilters - 1
    filters(i) = InputBox("Filter #" & i + 1)
Next i

'Apply multiple filters
With ThisWorkbook.Sheets("Sheet1")
    .Activate
    .Range("A1").Select
    .Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    For i = 0 To numberOfFilters - 1
        'ISSUE!
        Selection.AutoFilter field:=colNumber, Criteria1:=filters(i)
    Next i
End With
End Sub

Inputs: 1, 2, A, B

I realise that I'm selecting over Criteria1 multiple times. I've come across the following code:

Range("A1:D10").AutoFilter Field:=1, Criteria1:=Array("A", "B"), Operator:=xlFilterValues

The above code works perfectly, but I have to hard code the values "A" and "B". Is there a way to replace that with n user inputs?

Any help would be appreciated!

EDIT: Going a step further, how do I take number of columns as a user input and apply multiple filters to multiple columns?

Example: (Refer images)

Columns: 1, 2 ("Doc Type", "Year") Filters: 2 in column 1 ("A", "C"), 2 in column 2 ("2016", "2017")

I don't see any Redim on filters before stuffing values into the array. — user4039065
– user4039065, Commented Jun 11, 2017 at 14:24
@Jeeped, thanks for the tip! Filtering multiple columns seems to be my next task. I'm looking for solutions! — Adarsh Jeyes
– Adarsh Jeyes, Commented Jun 11, 2017 at 16:33

A.S.H · Accepted Answer · 2017-06-11 16:20:56Z

1

Try replacing all the With bloc by the simple:

  'Apply multiple filters
   Sheets("Sheet1").Cells.AutoFilter colNumber, Filters, xlFilterValues

Besides, as noted by @Jeeped you need to resize the filters array according to user input

Dim filters() As String ' <--- dont specify size here
....
'Number of filters to apply
numberOfFilters = InputBox("Number of filters to apply to Column " & colNumber)
Redim filters(0 to numberOfFilters - 1) As String  '<-- resize according to user input

edited Jun 11, 2017 at 16:20

answered Jun 11, 2017 at 14:21

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

2 Comments

Adarsh Jeyes Over a year ago

Thanks a lot for your answer! It worked. I'm new to VBA. Thanks for the tip on resizing the array!

Adarsh Jeyes Over a year ago

Good to be here! :)

Collectives™ on Stack Overflow

Apply multiple filters based on user input (VBA)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related