Advanced Filtering in VBA
Advanced Filter (under the Data > Filter menu) is used to show data from a list that meet a certain criteria. The AdvancedFilter method of the Range object can be use to perform the same action from VBA. You can greatly simplify filtering a list for the user by using a macro that does the work on predefined ranges.
This example uses the Orders table from the Northwind.mdb database.
The column headings are copied to row 2, which will be the column heading portion of the Criteria range. Two defined names are set up: fltCriteria for the Criteria range; and fltRange for the range to be filtered. Instead of defining names, you could set these ranged dynamically in the code to account for additional data to filter, or for additional rows in the Criteria range. For this simple example, the ranges are assumed to be static.
The Criteria range is formatted with a yellow interior and borders to provide a visual indication that the user can enter values in it. A button from the Forms toolbar is added that will run the macro to filter.
The user enters criteria in row 3 and clicks the Filter button to filter the range using this macro
Dim rngCrit As Range
Dim rngFilter As Range
With Sheet1
Set rngCrit = .Range(”fltCriteria”)
Set rngFilter = .Range(”fltRange”)
End With
rngFilter.AdvancedFilter _
action:=xlFilterInPlace, _
criteriarange:=rngCrit
End Sub
If the user want to see only customers whose ID starts with "V" and whose order dates are greater than January 1, 1997, the criteria range would look like this:
To show all the records again, clear the contents of the row 3 and click the Filter button.
sedaysen:
0028
30 November 2004, 4:21 amBrian Williams:
Hello Dick,
I am trying to code the AdvancedFilter subroutine using dynamically named ranges, as suggested above. I am following the convention that you posted in your dynamic ranges post on November 22 2004 . VBA returns an error message which says that I must use the AdvancedFilter method on lists which contain more than one entry. Have I miscoded the dynamic range name formulas?
Thank you,
2 December 2004, 7:31 pmBrian Williams
coworker:
can you tell me if the list range has to be on the same sheet as the criteria range?
5 April 2005, 7:09 pmRoss Flanigan:
I'm a relatively new user to VBA in excel, and found your code very useful and easy to follow. How would i go about copying the filter results to another worksheet?...and performing multiple advanced filters at the same time, copying the results of each filter to a different location?..If there's any fairly simple changes to the code above then I'd really appreciate learning what they are.
Best Regards,
Ross
11 August 2005, 4:10 amAnand Kumar Harith:
Hi Ross,
If the extract range specifies a range which is not in the same sheet in the copyToRange Argument, you can easily export the filtered values to another sheet.
Dim exRange as Range
Set ExRange = Sheets("Sheet2").Range("A:C") where
rngFilter.AdvancedFilter _
action:=xlFilterCopy, _
criteriarange:=rngCrit, _
copytoRange:= exRange
Sheet2 is the name of the sheet where you want the filtered list.
Trust this helps.
11 October 2005, 1:01 amJohnny:
Hi pls help me
16 December 2005, 2:31 amIt is possible to make the filter into a userform
so that it can display the search result into a listbox. thank
this author is crap:
try the code in EXCEL 2003 and EXCEL2007
the code would not work, waste all my time in typing the table
ADVANCE FILTER is crap !!! -- can't do a lot of usual stuff in VBA programming
With Sheet1
Set rngCrit = .Range(”fltCriteria”)
Set rngFilter = .Range(”fltRange”)
End With
====== DONT WORK ====
even more crappy code here >>>
rngFilter.AdvancedFilter _
action:=xlFilterInPlace, _
criteriarange:=rngCrit
so don't READ this stupid web page
19 November 2006, 12:47 pmhttp://www.dailydoseofexcel.com/archives/2004/11/22/advanced-filtering-in-vba/
Dick Kusleika:
Seth: It works fine for me in 2003. Just in case your comment is just spam, I've removed the link to your website.
19 November 2006, 10:24 pmMatt Cass:
Can anyone tell me if the above macro needs to have a specific range identified?
When I set this up with a basic test sheet and press the Filter button (using the above code exactly cut and pasted) I get an error-->
Run-time Error '1004'
Method 'Range' of Object '_Worksheet' failed
then when I Debug the line:
Set rngCrit = .Range(”fltCriteria”)
is highlighted.
I am very new to VBA and trying to learn more. This would be a very helpful Macro if I can get it working.
Thank you
21 September 2007, 9:01 pmSimon Herbert:
Matt - from the original article: "Two defined names are set up: fltCriteria for the Criteria range; and fltRange for the range to be filtered. Instead of defining names, you could set these ranged dynamically in the code to account for additional data to filter, or for additional rows in the Criteria range. For this simple example, the ranges are assumed to be static."
These names define where to look for the data and criteria. The following would work without defining the names, but is not as reusable:
24 September 2007, 3:07 am[vb]Range("A6:D1000").AdvancedFilter _
action:=xlFilterInPlace, _
criteriarange:=Range("A2:D3")[vb]
Simon Herbert:
(sorry, missed a /)
action:=xlFilterInPlace, _
criteriarange:=Range("A2:D3")
Nathan:
For the below code, what is the data to be input for the fltCriteria and fltRange? Any sample excel sheets to follow? Please help!
Sub DoFilter()
Dim rngCrit As Range
Dim rngFilter As Range
With Sheet1
Set rngCrit = .Range(”fltCriteria”)
Set rngFilter = .Range(”fltRange”)
End With
rngFilter.AdvancedFilter _
action:=xlFilterInPlace, _
criteriarange:=rngCrit
End Sub
17 December 2007, 8:58 pmTedla:
The code as writte in here DOES NOT work. Why don't people who can write write the stuff instead of codeheads who can barely put their thoughts to paper. It is sheer idiocy to assume people are plugged into your heads and therefore understand all the stuff they are supposed to do. Otherwise it is just to generate unnecessary question traffic so you can feel superior at someone elses expense. The examples at ozgrid or other parts of the net are much better documented and understandable.
13 June 2008, 11:47 amDick Kusleika:
Tedla: Thank you for your complaints. You misspelled "written", so I hope you won't judge me too harshly in the future. The problem with the code is with the double quotes. When I paste code into Wordpress, it converts my double quotes into something else. Oh, they look like double quotes, but they're not.
In case you aren't aware, I didn't write Wordpress. In fact, I don't know much of anything about PHP. You see, I'm an Excel programmer, idiot though I may be. I don't know why Wordpress screws up my code. If I did, I would fix it.
Despite your efforts, I still feel superior to most people. But in your case, I'll gladly refund the money I charged you to read my blog. Oh, that's right, it was free to you.
13 June 2008, 9:22 pmJon Peltier:
With all due respect (cough), most people who read this blog are familiar with the problem, know how hard Dick has tried to correct it, and know how to work around it with code copied from here and other web pages.
If I write a regular web page in pure HTML, I can suppress the corruption of various characters like < > " & by enclosing them within pre tags, i.e., . . . . Unfortunately, WordPress goes a bit beyond helpfulness. You can't really write pure HTML, because what you write goes through a filter. I suppose if I were more of a PHP guy I'd fix the filters myself, but then I wouldn't be an Excel/VBA guy. I was thinking that it wouldn't be too crazy to write a routine that converted VBA code into escaped HTML, replacing each special character with a code that WordPress will respect:
< & l t ;
> & g t ;
" & q u o t ;
& & a m p ;
There are many more, these are the common ones. I found handy tables of HTML character escape codes here:
14 June 2008, 6:40 amhttp://www.ascii.cl/htmlcodes.htm
http://www.lookuptables.com/
Jon Peltier:
Doh! My pre tags didn't survive. Should have been
"by enclosing them within pre tags, i.e., <pre>. . . </pre&rt;."
14 June 2008, 6:45 amJon Peltier:
One more time:
"by enclosing them within pre tags, i.e., <pre>. . . </pre>."
14 June 2008, 6:46 amDoug Jenkins:
What?
You can access this site for free?
Where's that "unpaid debts" thread?
I think Jon's idea wouldn't be too crazy at all.
Alternatively, couldn't all the people who post code on Wordpress sites get together and nicely ask them to fix it?
14 June 2008, 7:36 pmkanwaljit Singh Dhunna:
Hi Everyone,
Some help would be quite handy. I need to make VBA know whether the Filters are on or off, So that I can turn them off (in case they are on) before running my code (as running code on a filtered sheet will ruin my data). But am unable to do that. Any guidance !
Thanks n Regards
16 June 2008, 3:13 amKanwaljit
Hans Schraven:
"as running code on a filtered sheet will ruin my data"
16 June 2008, 6:51 amCan you show me what code can ruin data an a filtered sheet that can't an unfiltered sheet?
Neil:
kanwaljit,
16 June 2008, 10:54 amHave you tried the following:
With ActiveSheet
If .Filtermode then .ShowAllData
End With
mrt:
Hi Kanwaljit -
Look in the VBA help for "Filtermode"
MsgBox "Filter mode is on"
Else
MsgBox "Filter mode is off"
End If
...Michael
16 June 2008, 12:56 pmkanwaljit Singh Dhunna:
Hi Hans,
The code I use is supposed to paste only values in one the columns on sheet A. But if the filter are applied at the time of using the code, then the pasting gets hotch-potched. So you can understand what can go wrong. Even then if you can offer some guidance, I will surely supply the code.
Hi Neil and MRT,
Thanks for the help. It seems to do the job ! Thanks a lot.
Thnks n Regards
17 June 2008, 10:59 pmCA Kanwaljit Singh Dhunna
Hans Schraven:
@mrt
The one-liner
does the same job.
18 June 2008, 2:14 amJOHN ROBERTSON:
HI
I HAVE A VBA FORM WITH A LISTBOX(LB1). WHEN A LISTED ITEM IS SELECTED THE ASSOCIATED TEXTBOXES ON THE FORM PROVIDE FOR REVIEW/UPDATE DATA.
THIS IS ALL OKAY.
NOW i NEED TO FILTER THE LISTBOX ITEMS TO DISPLAY ONLY THE CURRENT ITEMS TO REVIEW/UPDATE.
THE FOLLOWING CODE FILTERS THE WORKSHEET PERFECTLY BUT DOES NOT CARRY TO THE LISTBOX ON THE FORM. CAN YOU HELP?
FILTER LIST TO CURRENT ONLY
Dim oWS As Worksheet
With Sheets("library")
.AutoFilterMode = False
.Range("A1:BO150").AutoFilter
.Range("A1:BO1").AutoFilter Field:=17, Criteria1:=">0"
End With
End Sub
TO RETURN TO FULL LIST
With Sheets("library")
.AutoFilterMode = False
.Range("A1:BO150").AutoFilter
End With
End Sub