Adv Filter Code

Worsty

Board Regular
Joined
Jun 29, 2004
Messages
183
Using this code from this post is there any way to use multiple columns for the criteria filter? I'm using dropdowns on a vba form for the user to select the filter criteria from, placing the result in C2 for the first dropdown (from the code), C3 for the second dropdown and so forth then running the macro from a button.

Code from the post:

Option Explicit
Sub Macro2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Criteria")
Set ws3 = Worksheets("Output")

Set rng1 = ws1.Range(ws1.Range("A3"), ws1.Range("G" & Rows.Count).End(xlUp))
ThisWorkbook.Names.Add Name:="myData", RefersTo:=rng1

Set rng2 = ws2.Range(ws2.Range("C1"), ws2.Range("C" & Rows.Count).End(xlUp))
ThisWorkbook.Names.Add Name:="myCriteria", RefersTo:=rng2

Set rng3 = ws3.Range(ws3.Range("A2"), ws3.Range("G" & Rows.Count).End(xlUp))

Set rng4 = ws3.Range("A3")

rng3.ClearContents 'clear the Output worksheet

' do the filter
ws1.Range("myData").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws2.Range("myCriteria"), _
CopyToRange:=ws3.Range("A2"), _
Unique:=False

' Sort the filtered data
With ws3.Range("A3").CurrentRegion
.Sort Key1:=.Range("C3"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

With rng3
.WrapText = False
.Columns("A:G").AutoFit
End With

End Sub
--------------------------------------------------------------------------------
:unsure:
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
You can change this part of the code

Set rng2 = ws2.Range(ws2.Range("C1"), ws2.Range("C" & Rows.Count).End(xlUp))

the "C" refers to column C, so you can change that to whatever column you want to use, for example, like this:

Set rng2 = ws2.Range(ws2.Range("C1"), ws2.Range("F" & Rows.Count).End(xlUp))
 

Worsty

Board Regular
Joined
Jun 29, 2004
Messages
183
Thanks for replying.

I probably didn't explain myself. I want to filter using multiple criteria that resides in multiple columns. So for example:

Column A contains criteria 1, Column B contains criteria 2, Column C contains criteria 3, so on.

From dropdown 1 which is poplulated with the unique data from Column AI want to pick a criteria, then from dropdown 2 populated with unique data from Column B I want to pick an addtional criteria, and the same for dropdown 3 from Column C unique records.

How would I do that?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top