Extract data using Advanced Filter and VBA

Posted on November 27th, 2012 in Excel Howtos , VBA Macros - 67 comments

In this post we will learn how to use the Advanced Filter option using VBA to allow us to filter our data on a separate sheet. This has been requested by a lot of our readers and here is how we will use them.

Filter records using advanced filter and vba

What we need to get this done.

1. Some data that we need filtering on.
2. Define what options we need as drop down lists
3. A cup of coffee

 

In the sample data, I have defined 4 options to be available as drop down list; this has been done by creating a new sheet called as “Master”. I then copied the existing columns data into this sheet and used the Remove Duplicates feature to get the unique list of items that was required for the drop downs.

The named ranges were created using the INDEX function as shown below

Named Range Formula
prd =Master!$A$2:INDEX(Master!$A:$A,COUNTA(Master!$A:$A))
rgn =Master!$B$2:INDEX(Master!$B:$B,COUNTA(Master!$B:$B))
cust =Master!$C$2:INDEX(Master!$C:$C,COUNTA(Master!$C:$C))
agnt =Master!$D$2:INDEX(Master!$D:$D,COUNTA(Master!$D:$D))

Now we need to setup the sheet where we need the filtered data to be displayed. Headings were put in cells B5 to B8 and the drop down (using the Data Validation—List) feature was put in cells C5 to C8. Now we need to create or criteria fields in the RawData sheet, this is a requirement and cannot be any place else. When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA and telling Excel where to put the filtered data. I used the cells M1 to P1 to define the headings and cells M2 to P2 to get the actual options from the “Filter Sheet”

Cells Formula
M2 =Filter!C5
N2 =Filter!C6
O2 =Filter!C7
P2 =Filter!C8

Macro to run advanced filter and extract data


Sub FilterData()
Sheets("Filter").Select
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Sheets(“RawData”).Range(“Table1[#All]“).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True

Columns.AutoFit
Range(“B10″).Select
End Sub

First we ensure the current filtered data (in any) is cleared out before we run the code again and then we get the new filtered data from cell B10 onwards. Now let’s understand the actual code that filters our data here.


Sheets("RawData").Range("Table1[#All]").AdvancedFilter
Action:=xlFilterCopy,
CriteriaRange:=Sheets("RawData").Range("M1:P2"),
CopyToRange:=Sheets("Filter").Range("B10"),
Unique:=True

We converted our raw data into an excel table (Structured Reference Structured Reference), by doing this we no longer need to know how many rows our data actually goes down to, the “Table1[#All]” takes care of that for us.

We also need to specify that our data is in another sheet and we are trying to run Advanced Filter on that data range, this is done using the first line ” Sheets(“RawData”).Range(“Table1[#All]“).AdvancedFilter “.

Next we specify the action that we need which is Copy in our case, the other option is “xlFilterInPlace” which would filter right on our data itself.

Then we have specified the Criteria Range (which needs to be on the same sheet where the data is).

And finally we have specified where the output has to be sent to by using : “CopyToRange:=Sheets(“Filter”).Range(“B10″)”

We have also made sure that only Unique records are returned to us by turning Unique:=True.

Download Advanced Filter Demo File

Click here to download the demo file & use it to understand this technique.

Do you use Advanced filters to extract sub-sets of data?

Advanced filters are very powerful and very simple to setup. I use them often to quickly extract what I want.

What about you? Do you use them often? Please share your experiences, techniques & ideas using comments.

Learn more about extracting / consolidating data using VBA

Data extraction and consolidation are one of the most common activities done by reporting professionals & analysts. No wonder we speak about these areas a lot here too. Please check out these pages to learn more:

Want to lean more? Consider joining our VBA Classes

If you would like to learn more about VBA programming, Excel automation, creation of user forms, manipulating data in Access thru Excel etc., consider joining our online VBA Classes. This step-by-step program helps you become awesome in VBA.

Click here to know more & Join our classes.

Your email address is safe with us. Our policies

Written by Vijay Sharma
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

67 Responses to “Extract data using Advanced Filter and VBA”

  1. lockdalf says:

    one question though, I I were to use this kind of filtering for a report, would it not be better to remove the original formatting of the data table and use target formatting instead? I am sure it can be done but I do not know how, so you can consider this a question to be answered… ;o]

  2. Jon says:

    I’ve actually created a whole add-in surrounded the advanced filter. I basically made it so it is really easy to filter data with a “text wrapper” form. My add-in is more for general purpose. I like how you did it here – if you are doing the same thing every time. Pretty clean.

  3. Andrew says:

    This is great.  I’ve been looking for a way to do this for a while.  One question, what if you don’t want to as specific as the filter goes meaning maybe you want to see all the calls for Agent Bond?  

  4. CMC says:

    “Now we need to create our criteria fields in the RawData sheet, this is a requirement and cannot be any place else”
    This is not a requirement: I ran your Advanced Filter with a copy of your criteria in Filter sheet and it worked perfectly (no error messages and produced the same number of records of your macro filter).
    “When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA”
    It’s not strictly necessary to use VBA: you can overcome this message by executing the Advanced Filter command from the sheet where you are going to put the filtered records (in other words, activate the output sheet before you run the command from Excel)
    Macro Code:
    Range(“B10″).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    You are clearing the output range before the execution of Advanced Filter command: this is not necessary because this commands clears the output area of the last execution before it generates the new output of filtered records.

    I hope this helps.  

    • Waqas Abbasi says:

      Hi Friends,
      I am unable to practice this nice trick though i followed all procedure. can anyone help me please.I some one tell me his email id, i’ll send my file for correction.please someone help me

  5. Chris Cowman says:

    Hi – Is there a way to add an “All” to the drop-downs to show every “Product”, but still be able to see specific “Regions”, “Cust Type”, etc.?

    • Kdu Bonalume says:

      Yes, try this:

      For blank data, use “=” (no quotation marks);
      For Non-blank data, use “<>” (no quotation marks);
      For all data, use blank (nothing inside the criteria cell);

      Kdu.

      • Chris Cowman says:

        Hi Kdu – Not sure what you mean and where you would use the =, <>, and blank…

        • Kdu Bonalume says:

          I meant that you should add these characters inside the cells of the filter.
          And when you want all the results for a specific criteria, you kept it without data.

          Got it?
          If you want all products, write nothing inside the criteria cell for product and then write your other criteria for regions etc. 

    • lockdalf says:

      I have done a simple adjustment to the lists that are the source for the drop downs. I have simply added an asterisk to the beginning of the list. Since ‘*’ replaces any character it works like a charm

  6. Kdu Bonalume says:

    Hi Chandoo, once more a very useful article.

    My workmate and I commonly use this feature to filter data by several criteria but I’d like to make a suggestion on the VBA code so it would work on Excel 2003 as well.

    Once excel 2003 doesn’t have Table support, you should change this lines of code:
     
    Range(“B10?).SelectRange(Selection, Selection.End(xlToRight)).SelectRange(Selection, Selection.End(xlDown)).SelectSelection.Clear

    by these ones:

    Dim tbl as Range
    Set tbl = Range(“B10?).CurrentRegion
    tbl.Select ‘You can delete this line, it will just show you what each step doesSet tbl = tbl.Offset(1, 0).Resize(tbl.Rows.Count – 1, tbl.Columns.Count)
    tbl.Select ‘Same as before
    tbl.Clear

     You’ll need also change this line:
    Sheets(“RawData”).Range(“Table1[#All]“).AdvancedFilter Action:=xlFilterCopy,CriteriaRange:=Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True

    By a different reference to the table where your raw data is, using for example the top left-most cell in the range:
    Sheets(“RawData”).Range(“A1″).AdvancedFilter Action:=xlFilterCopy,CriteriaRange:=Sheets(“RawData”).Range(“M1:P2″), CopyToRange:=Sheets(“Filter”).Range(“B10″), Unique:=True  

     So, that’s it! Now it works on Excel 2003!

    Kdu. 

  7. Tim says:

    I have built custom function for this as I use it all the time and want to be able to pass all the arguments to the advanced filter.  Code below.  (note is uses a central error handler not associated with the code)

    Public Function DataFilter(rngData As Excel.Range, rngDest As Excel.Range, Optional rngCriteria As Excel.Range, _
                                Optional blnUnique As Boolean) As Boolean
    ‘   Set Routine namE
    msProc = “Scorecard”
    gsDesription = “Commencing Scorecard Build…”
    On Error GoTo ExitProc
    If blnUnique Then
        rngData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=”", CopyToRange:=rngDest, Unique:=True
    Else
        rngData.AdvancedFilter Action:=xlFilterCopy, criteriarange:=”", CopyToRange:=rngDest, Unique:=False
    End If
      Exit Function
    ExitProc:
    success = False
    Call bCentralErrorHandler(msMODULE, msProc, , False)
    End Function

    • CMC says:

      As I said before, it’s not necessary to clear the output range, but if you want to do it, yo don’t need to Select it; this statement is simpler and works fine:

      Range(Range(“B10″), Range(“B10″).End(xlToRight).End(xlDown)).Clear

      Hope this help.      

  8. zur says:

    Why we need elaborate coding?

  9. Mike H says:

    To clear the output range why not use Range(“B10″).CurrentRegion.Clear instead of
       
        Range(“B10″).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Clear

    • Jon says:

      Mike H,

      It is bad form to use the select method unless absolutely necessary. It takes longer to actually select cells/sheets/ranges than to just reference them. In this case it probably isn’t as big of a deal since it is selecting so few things, but it is still important to be consistent in your coding methods.

      I would be interested in using Kdu’s method without clearing, I didn’t know you could do that – but I wanted to check it out first. 

      • Hui... says:

        @Jon

        You’ve misread, Mike H’s post

        Mike H suggested exactly what you said and offered an alternative using the Current region modifier

        • Jon says:

          @Hui,

          Ha! Yes, I did. :) 

          I also tried Kdu’s solution of just doing the filter without clearing. It doesn’t work if the latest filter has fewer items. That’s is what I was thinking, but thought maybe there was something that I was missing, good to know that clearing is important! 

          • Kdu Bonalume says:

            Hey guys,

            The code I provided will declare a variable to be set with the data range and when i use the “.offset(1,0).resize(..” statement, i’m setting the variable with just the DATA from the filtered range and after clearing it up, because without it VBA will also clear the Headers row.

            So, these are the steps that your code must follow:

            - Select the current filtered range;
            - Clear it (without the headers row);
            - Filter the Raw Data using the criteria defined on the other sheet;

            I’m doing this keeping the header row because actually i didn’t t check if the advanced filter also brings the headers in the result. If so, just forget the statements where i’m removing the headers from the range to be clear.

            Kdu. 

          • CMC says:

            Try this:

            - In VBA code, comment Clear Statement

            - Change CopyToRange:=Sheets(“Filter”).Range(“B10″) to

            CopyToRange:=Sheets(“Filter”).Range(“B10:K10″) (note : K10)

            - Execute your macro

            I tried this with several values of Product and it worked fine in all cases without clearing the output range before running the Advanced Filter.

            It works too if you execute the Advanced Filter from sheet ’Filter’      

            HTH

               
                  
                  
               

          • Jon says:

            @CMC,

            That does it! It removes all old data. Pretty nice. I’ll have to keep that one in mind. 

  10. Sunil says:

    This in an excellent article though I have a question, what if you want selected data from the raw data table….for example if I just want values pertaining to column “Call Duration” and “Call ID” only…any suggestions on how can I do that. Most of the time while working with excel data you just need selected data instead of whole table….
     

  11. Mike H says:

    Hi Jon and CMC

    Am I missing something ?

    I have remmed out the clear part of the code and added k10 to code as recommended however when changing from a large extract to a small I still get formatting left on screen of the extra lines.  To try and replicate – Laptop, North, SME, Agent Vinod and run extract code then keep first three same but change to Agent Mary and re run again.  When doing for Agent Mary with less data have six extra blank formatted lines left on table and in addition rows 17 to 20 all formatted same background pale blue not alternate as should be.  If you get the same does this mean we do need to clear prior data to ensure layout / formatting is correct?
      
     

  12. RichW says:

    So how is it that you can not use Advanced Filter to manually paste data to a different sheet but if you use VBA it is OK??

    • Jon says:

      Per Microsoft you <em>can</em> copy to another sheet without VBA.

      http://support.microsoft.com/kb/117643

      <em>When you choose the Advanced Filter option from the Data menu, you can only copy filtered data to another location if the destination sheet is the active sheet.</em>

    • Hui says:

      @RichW
      Unfortunately the implementation of Excel Functions is not handled consistently within Excel, The Name Manager and VBA, although I am sure you expect it is
      I can show you cases where using a simple Excel Function on a worksheet works fine, But when used in the Name Manager it crashes Excel
      It’s been reported to MS since Excel 2007 and the bug still exists in 2013.
      Obviously there to busy implementing bling at the expense of doing something right!
      </End Rant>
       
       

  13. Chandoo, if I was a girl, I would have blown kisses on you for this. I spent my whole Monday trying to do this because I need to prepare a dashboard showing dynamic organizational hierarchy according to the audience level in company. Suppose if a regional manager views the report, he should be able to see his zonal manager performance or of a zonal manager views report, he should be able to his area manager performance. Though I was not in favour to use VBA in my dashboard, but this is going to be super helpful. You made another day for me. Thanks lots. Buy yourself a lette from my side :)

    P.S. I wish Excel introcuces some function like {=DISTINCT(ManagerNames)}  in next versions. That would be far helpful on such cases. Please come up on it, if you got something.

    With thanks, Ashish 

  14. Ken ?-del says:

    Make the filtering completing dynamic without requiring the user to click the “Get Data” button by capturing the Change event on the Filter sheet. This makes the user experience a lot more responsive and engaging!

    Add the following VBA to the code behind the Filter sheet where the range “filters.All” is defined as Filter!C5:C8

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Me.Range(“filters.All”)) Is Nothing Then Exit Sub
       
        Application.ScreenUpdating = False
        FilterData
        Application.ScreenUpdating = True
       
    End Sub    

    • MikeC says:

      Sorry but i don’t understand this instruction “Add the following VBA to the code behind the Filter sheet where the range “filters.All” is defined as Filter!C5:C8″

      • CMC says:

        This is a worksheet level code. Right Click in Filter sheet tab, choose See Code and copy all the statements from Private Sub Worksheet_Change ….

        HTH

  15. Senthil says:

    I was scrolling through data in “RawData” sheet. I noticed that when the table was scrolled down, Column titles automatically turned from A to “Call ID”, B to “Date Time”… etc (works like freeze panes but only for the table)

    Can you please tell me how to do it?  

    Thank you,
    Senthil 

    • CMC says:

      It’s because RawData is not a (simple) range but it has been defined as a Table (this is a new concept since Excel 2007).

      Google for “Tables in Excel 2007″ to learn more about Tables.

      HTH,

      CMC   

  16. Waqas Abbasi says:

    Hi Friends,

    I am unable to practice this nice trick though i followed all procedure. can anyone help me please.
    I some one tell me his email id, i’ll send my file for correction.
    please someone help me 

  17. mma173 says:

    I want to do the same thing as in the article except for using checkboxes instead of dropdown menus. Doing so more than a single criteria can be chosen e.g. the one can tick Desktop and Laptop.
     

  18. Ida says:

    Hi,

    I am using advanced filter in my code, but the problem is that when the data is copied, it copies just values, and I need it to copy hyperlinks as well. Is there a way to copy links as well?

    Please see my code for reference below.

    Thank you in advance,

    Ida

    Range(“rangeA”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    “B8:R9″), CopyToRange:=Range(“B10:R1000″), Unique:=False

  19. alejandro says:

    this filter is simply, great…this tool i use everyday in my macros….

  20. Marco says:

    Hi, thanks for this very useful filter… I have a little question… I’m trying to filter SAP transaction and it is common to have fv50 and fv50n, if I try to filter fv50 but the filter brings the info of both transactions. Can I specific that only bring exact values?

  21. David Rahman says:

    Hi Chandoo,
    I have studied your artcle about Advanced Filter. Your have explained very understandble way. You have a wonderful teaching capacity.

  22. blanco says:

    Hi,
    Few things for clarifications please since I’m new with macros and I could not let this one work.

    1) Where do I put the Named range Formulas?
    2) How does the Get Data button/icon get created?
    3) What’s the final macro script to get it to work? Can’t follow the thread for revisions.

    Thanks!

  23. Alex says:

    Hi,
    Is there a way to limit the results to a certain number of rows so it will not update the cells below the desired output area?
    For example, I’m not expecting more than 20 items for every query so I might limit the result to that number.
    I hope there’s a way. :-/
    Thanks!

  24. Santosh says:

    Hi chadoo,

    I have been doing this job before also. But i have a small requirement. After filtering the data according the given criteria, i need a total column in the last cell.

    for example, in your table shown above, after filtering the data how to get total of the “Call-duration” and “Up-sell” fields. Further, if you give serial No., then how to get serial numbers for the filtered data.

    Your comments in this regard would be very helpful for me.

    Regards,

    Santosh

  25. aaron says:

    This tutorial is great. Quick question though, I have two filters location and data type. Right now I have 30ish columns of data and it is organized by SKU with marketing information, basic, pricing and dimensions (these are my data types). Depending on the data type there are several blank columns. Is there anyway to have those columns not show up? Like when I choose basic I only need columns B-F can I get rid of G through AH?

  26. Alfonso says:

    I have the same question as aaron, how can I copy only certain columns instead of all the table?

  27. […] large data sources than formulas, so use them wherever possible. See Chandoo’s post Extract data using Advanced Filter and VBA and Daniel Ferry’s great post Excel Partial Match Database Lookup for examples on the […]

  28. […] Hi Jemma Topics like this have been covered before. Various websites list the possibilities, including an Advanced Filter: Extract data using Advanced Filter and VBA | Chandoo.org – Learn Microsoft Excel Online […]

  29. EJDee says:

    Hi Chandoo,

    Great website, it’s number one on my favorites! My question is I used this advance filter and made some tweaks by using a text box instead of drop downs for user input. Works like a charm. What I need to do though is only return certain certain columns not all of them. Example key fields are: CustID, CustName, CustAddress, CustCity, CustState, CustZip. I want all except the “CustID” to copytorange, but keep in mind user is using “CustID” to query. Any help would be greatly appreciated!

    PS. I thought of using formatting text to white, but was hoping for better solution.

  30. Santa says:

    Hi Chandoo, Happy New Year to you & your family!!!

    Need your help here. I have a similar data but i want to filter between two dates. The date column is in the format “dd-mmm-yy h:mm AM/PM”

  31. Charlie says:

    Hello using this workbook as an example, is there a code that can be applied to a separate button labelled ‘See all’ so that the table displays all the raw data from sheet 1, in to sheet 2. Its just to make it a bit more user friendly for my colleagues so they can look at one sheet with all the information on it.

    Thank you

  32. Anubhav says:

    Could you please elaborate
    “The named ranges were created using the INDEX function as shown below”

    Novice here!!!

  33. sam says:

    Me too!

    What do you mean by:

    “The named ranges were created using the INDEX function as shown below”

    greetings!!!

Leave a Reply