47 thoughts on “Advanced Filtering in VBA”

1. 0028

2. 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

3. can you tell me if the list range has to be on the same sheet as the criteria range?

4. 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

5. 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.

6. 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

7. 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/

8. Seth: It works fine for me in 2003. Just in case your comment is just spam, I’ve removed the link to your website.

9. 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

10. 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")

11. (sorry, missed a /)

    Range(“A6:D1000”).AdvancedFilter _
    action:=xlFilterInPlace, _
    criteriarange:=Range(“A2:D3”)

12. 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

13. 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.

14. 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.

15. 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/

16. Doh! My pre tags didn’t survive. Should have been

    “by enclosing them within pre tags, i.e., <pre>. . . </pre&rt;.”

17. One more time:

    “by enclosing them within pre tags, i.e., <pre>. . . </pre>.”

18. 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?

19. 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 :)

20. “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?

21. kanwaljit,
    Have you tried the following:
    With ActiveSheet
    If .Filtermode then .ShowAllData
    End With

22. Hi Kanwaljit –

    Look in the VBA help for “Filtermode”

    If Worksheets(“Sheet1”).FilterMode = True Then
        MsgBox “Filter mode is on”
    Else
        MsgBox “Filter mode is off”
    End If

    …Michael

23. 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

24. @mrt

    The one-liner

    MsgBox “Filter mode is o” &amp; iif(sheets(1).filtermode,“n”,“ff”)

    does the same job.

25. 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

    Private Sub cmdCurrent_Click()
    Dim oWS As Worksheet

    With Sheets(“library”)
        .AutoFilterMode = False

        .Range(“A1:BO150”).AutoFilter
       
        .Range(“A1:BO1”).AutoFilter Field:=17, Criteria1:=“&gt;0”

    End With

    End Sub

    TO RETURN TO FULL LIST

    Private Sub cmdAll_Click()
     With Sheets(“library”)
         
            .AutoFilterMode = False
       
            .Range(“A1:BO150”).AutoFilter
       
        End With
    End Sub

26. Thanks a lot, it really helps!

27. 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.

28. 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

29. 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!!

30. 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.

31. All, Can you helpl

32. All is it possible to use autofilter to have the same results as like counting tthe distinct values? Like in SQL yo

33. “When I paste code into WordPress, it converts my double quotes into something else.”

    Please add that to the top. Code was simple enough and didn’t occur to me to double check the copy/paste.

34. Can I please get a code for :

    I need a filter function, the inputs for the filter are in three combo boxes. The filtered results should open in a seperate window.

    Please help
    Manju

35. Hi all,

    I was wondering if someone could help with some VBA.

    I have a list of information that I would like to filter with multiple criteria in one column. So, for example column B would contain numbers and I want to filter it to display just those rows who’s values are, say, 10, 20 and 30.

    I have the criteria, currently, initialised as an array within the VBA code; the values do not exist on the spreadsheet so I can’t reference a Range on the sheet. I’ve spent a bit of time tinkering around with various bits, but have had no success at the moment!

    Any pointers would be gratefully received,

    Thanks in advance …

    Steve.

36. This snippet – Looks like exactly what I was looking for when google search results brought me to this page so thanks to all who contributed it:
    sub toggle_dataFilters_onOff

    If Worksheets(“Sheet1?).FilterMode = True Then
    MsgBox “Filter mode is on”
    Else
    MsgBox “Filter mode is off”
    End If

    FYI — I don’t have a copy of the old code I used to use, but I think under office 97/2003 Excel, there may have been a different property. As I no longer have access to the older software, I can only verify this works on Office 2007 (even in XLS sheets that are supposedly compatible with the older release). Past experience shows you must test code in each release you plan to use it, otherwise the unexpected can occur.

    But in the spirit of sharing – here’s what I did with above code to use it in my sheet and a little explanation on it:

    Sub toggle_dataFilters_OnOff()
    ‘ To use this code – create a replacement for goTo_startOfProdCol or simply put in a range that positions
    ‘ the active cell to start of first column in your sheet

    ‘ this sub activates the “data area” of a workbook where all sheets have the same continuous data area
    ‘ starting with a “products column”. relative references make the macro re-usable on any sheet of same setup
    ‘ If condition will turn filters off if filter is already on
    ‘ code is then called by button called “Turn filters on/off” re-used on each sheet of the workbook:

    If ActiveSheet.FilterMode = True Then
    ‘ MsgBox “Filter mode is on”

    goTo_startOfProdCol
    ‘ this is my own sub – positions activecell at top of my “products” column
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ‘ this extends to end of sheet – where my data area ends

    Selection.AutoFilter
    Else
    ‘ MsgBox “Filter mode is off”

    Selection.AutoFilter
    End If

    End Sub

37. This code does work! Anybody who says it doesn’t probably haven’t set their ranges properly

    The header must be included for the criteria and the filter range! How else would excel be able to match the fields with the filters.

    I have just tested this method, and used it for my own purposes, runs very fast and very flexible

38. And the filter criteria cannot contain blanks!

39. Sub ApplyFilter()

    Dim rngCriteria As Range
    Dim rngFilter As Range

    Set rngCriteria = Sheets(“CLIENT FILTER”).Range(“FilterCriteria”)
    Set rngFilter = Sheets(“DATA”).Range(“FilterRange”)

    rngFilter.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=rngCriteria

    End Sub

    Sub ShowAll()

    If Worksheets(“DATA”).FilterMode = False Then
    MsgBox “There are no filters applied”
    Else
    Worksheets(“DATA”).ShowAllData
    End If

    End Sub

40. works like a charm!!

41. Please Help!

    I am very new to VB coding. I have the following code, crude as it may be, to filter one sheet and copy data to another workbook sheet.
    I would like it to only copy the first 4 colums (cells) in the row found. Everytime I change the copy argument from EntireRow to anything
    else it gives me an error.

    Thanks in advance for any help

    Sub Test2()

    Dim WS As Worksheet
    Dim WSNew As Worksheet
    Dim rng As Range
    Dim Str As String
    Dim wbk As Workbook

    ‘strFirstFile = “c: empPavement MarkingTest.xlsm”
    strSecondFile = “c: emp2012 Fast Dry Test.xlsm”

    Set WS = Sheets(“COSHOCTON”) ‘<<< Change as needed
    Set rng = WS.Range(“A4:Z100?) ‘<< 0 Then

    Err.Clear
    End If
    On Error GoTo 0

    End Sub

42. Sorry, whole code did not appear. I am using AutoFilter to do filtering.

    <
    Set WS = Sheets(“COSHOCTON”) ‘<<< Change as needed
    Set rng = WS.Range(“A4:Z100?) ‘<<

43. Crikey, haven’t had a good laugh like that in ages. “This code doesn’t work” lmfao.

    Nice one, Dick. It’s years since that you (and others, naturally) helped me along the road to learning vba and i’m still grateful for the time and effort put in and general altruism you’ve shown.

    Thank you. (you to Jon)

44. Oh, and for those above that have asked… The data can be on a different sheet to the output if you are using named ranges.

    I use code like this:

    Sub RC_FilterCopy()

    Dim Source As Range
    Dim Criteria As Range
    Dim Target As Range

    Set Source = Sheet1.Range(“A1”).CurrentRegion
    Set Criteria = Sheet2.Range(“A1”).CurrentRegion
    Set Target = Sheet2.Range(“A9”)

    Target.CurrentRegion.Clear

    Source.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Criteria, _
    CopyToRange:=Target, _
    Unique:=False

    End Sub

    Not saying this will work, just use it as pseudo-code to get the general idea.

45. Thanks dude! Worked great. Forget those negative comments. Might want to clerify explicitly what fltCriteria (A2:D3) and fltRange (A5:D835) in your example. Other than that, thanks for the post.

46. Code rage! It’s happening a lot these days.

    Dick your abuser is beyond belief. Surely that was a joke by one of your colleagues to wind you up? But what ever it was you were magnificent and played a straight bat. Very funny post that lightened my day. I also think 2010 and 2013 VBA (can’t talk for 2007) for advanced filter is causing unknown problems compared to 2003 and not accepting variable addresses for criteria and extract ranges. I will continue googling but now with a lighter heart.

47. Well I found my problem 2003 vs 2013 Adv Filter VBA

    In 2003 I had dynamic criteria in a cell like this next line. The F8 is a real date value that changes as the user chooses a period – I formatted this as text so I could read the date and thought why not as the statement was a string anyway. I then Set a variable to this cell’s contents and used this variable in my .AdvancedFilter statement’s criteria.

    =”>=”&TEXT(F8,”dd/mm/yy”) Worked great.

    But now in 2013 I have found VBA ignores this criteria (and I suspect so does in 2010).

    So I changed it to this and it works again

    =”>=”&F8

    Hope this saves someone a few hours of frustration that I just spent. I think the laughing at the abuser paved the way.

Posting code? Use <pre> tags for VBA and <code> tags for inline.