Filter range based on another range

Question

1.00/5 (1 vote)

See more:

I want to filter a range based on another range, I want to give input range ,criteria range and field through a input box ,When I tried the following code, filter with " unselected all" is coming

Thanks for the reply..

What I have tried:

VB

Sub Multiple_Filter()
Dim Avalue As String
Dim a As Range
Dim crange As Range
Dim mainrng As Range
On Error GoTo 12
Set mainrng = Application.InputBox("Select the range ", "Advanced_Filter", Selection.Address, Type:=8)
Set crange = Application.InputBox("Select  Criteria Range ", "Advanced_Filter", Selection.Address, Type:=8)
Set filter_column = Application.InputBox("Select the first cell in the column where the Filter is to be applied", "Advcancesd_Filter", Type:=8)
first_colomn_number = mainrng.Column
filter_coloumn_index = filter_column.Column
d = filter_coloumn_index - first_colomn_number + 1 'to get the index for field
For Each cell In crange
Avalue = """" & cell & ""","
Next cell



len_avalue = Len(Avalue)
Avalue = Mid(Avalue, 1, len_avalue - 1) ' to remove the last "," from Avalue
 mainrng.AutoFilter Field:=d, Criteria1:=Array(Avalue), Operator:= _
        xlFilterValues


End Sub

Posted 16-Sep-19 23:38pm

Member 14588327

Updated 10-Oct-19 7:11am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 10-Oct-19 9:35am

Why InputBox?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-10-10T07:12:00

The way you are trying to filter data is wrong. Please, read the documentation: Range.AutoFilter method (Excel) | Microsoft Docs[^]

1. I'd avoid of using below code, due to user interaction.

VB

Set mainrng = Application.InputBox("Select the range ", "Advanced_Filter", Selection.Address, Type:=8)

When user hit Cancel, your code will return error.

2. Note, that below code:

VB

For Each cell In crange
Avalue = """" & cell & ""","
Next cell

replaces Avalue variable content in each iteration.
If you would like to build Criteria1, you have to create an array of variant:

VB

Criteria1:=Array("1", "3", "Seattle", "Redmond")

Let's find out what is passed to Criteria1 input parameter:

VB

Dim Avalue As String
Dim Vvalue As Variant

Avalue = "A,B,10,20"
Vvalue = Array(Avalue)

For i = LBound(Vvalue) To UBound(Vvalue)
    Debug.Print Vvalue(i)
Next

Tip: comma separated string instead of an array of variant

Good luck!