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.
0028
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,
Brian Williams
can you tell me if the list range has to be on the same sheet as the criteria range?
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
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.
Hi pls help me
It is possible to make the filter into a userform
so that it can display the search result into a listbox. thank
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
http://www.dailydoseofexcel.com/archives/2004/11/22/advanced-filtering-in-vba/
Seth: It works fine for me in 2003. Just in case your comment is just spam, I’ve removed the link to your website.
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
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:
Range("A6:D1000").AdvancedFilter _action:=xlFilterInPlace, _
criteriarange:=Range("A2:D3")
(sorry, missed a /)
action:=xlFilterInPlace, _
criteriarange:=Range("A2:D3")
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
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.
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.
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:
http://www.ascii.cl/htmlcodes.htm
http://www.lookuptables.com/
Doh! My pre tags didn’t survive. Should have been
"by enclosing them within pre tags, i.e., <pre>. . . </pre&rt;."
One more time:
"by enclosing them within pre tags, i.e., <pre>. . . </pre>."
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?
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
Kanwaljit
“as running code on a filtered sheet will ruin my data”
Can you show me what code can ruin data an a filtered sheet that can’t an unfiltered sheet?
kanwaljit,
Have you tried the following:
With ActiveSheet
If .Filtermode then .ShowAllData
End With
Hi Kanwaljit -
Look in the VBA help for “Filtermode”
MsgBox "Filter mode is on"
Else
MsgBox "Filter mode is off"
End If
…Michael
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
CA Kanwaljit Singh Dhunna
@mrt
The one-liner
does the same job.
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
Thanks a lot, it really helps!
Be aware, Microsoft Excel 2007 behaves different to Excel 2003 if Filtering is done in VBA.
Excel 2007 ALWAYS requires us-notation of numbers and dates and does not use any local settings in Filtering.
Excel 2003 does use local settings.
If You filter with value ranges such as “< 0,1″ in Excel 2003 on a german Version it will work but not in Excel 2007.
In Excel 2007 the Advanced Filter works if done by hand but not in VBA. There “< 0.1″ has to be used!
Improved confusion in Excel 2007.
Thanks for this code, very helpful to a non VBA person. All works fine except that when i use >= to on a date column it refuses to return any information,(all records hidden including dates equal and over the filter date) any advice you could give would be most appreciated
Can Anybody Help me???
I am trying to create a search tool within excel and I am completely stuck. In my workbook I have two different worksheets; One contains a database and the other one contains two different search criteria and a Results table. I am trying to create a tool that will search the database for the different search criteria and then copy the relevant entries into the results table. I am very new to VB coding and was wondering if anyone could help me with the code that is needed to do this. I would be extremely grateful for any help, as i am tearing my hair out!!
Hi,
I have this code that works in Excel 2003.
Sub UpdateSysInfo(ByVal SbTtl As Range, ByVal AFfld As Integer)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Worksheets(”SystemInfoReport”).ShowAllData
Worksheets(”SystemInfoReport”).Range(”SysInfoDb”).AutoFilter field:=AFfld, Criteria1:=”"
Application.EnableEvents = True
Worksheets(”SystemInfoReport”).Calculate
SbTtl.Value = Range(”SysInfoAgencyNameSbTtl”).Value
End Sub
But fails during compile in Excel 2007 with a Runtime error 1004 for “ShowAllData” method of Worksheet class failed with the following highlighted during debug “Worksheets(”SystemInfoReport”).ShowAllData”
I have the worksheet opened in ‘compatablity mode’ since it must work in a 2003 environment.
I have googled and attempted to read up on this but still come up empty, so I would appreciate any help.