fbpx
Search
Close this search box.

Extract data using Advanced Filter and VBA

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

126 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

    • Anthony Aguirre says:

      Did you find a solution to the hyperlink problem? I'd love to use this technique but it won't work w/o the links.

      Thanks.

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

  34. Jennie says:

    Hi, i am very new to VBA and am wanting to use this for a simple report i have. I followed all the steps however i keep getting the following error message: Runtime Error '1004' Application defined or object defined error.
    when i debug it highlights in yellow the following piece of code:

    Sheets("raw data").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Sheets("raw data").Range("M1:O2"), CopyToRange:=Sheets("Filter").Range("A12"), Unique:=True

    Can you please help me identify what i have done wrong. I am very new to VBA and unsure of how to troubleshoot errors.

    Thanks heaps for your help in advance.

    • Jennie says:

      i just found why this wasnt working. my table name didnt match. sorry to waste your time.
      Thanks again love your work

  35. Jenn says:

    hi,
    i have done this successfully but i am now wondering how to implement a data range for this dataset. so for example: my data set has the following filters applied successfully: category and business unit. because my table of data is so extensively long i wish to add another level which they can filter records my date. However, i only have 1 date field in my table Date Raised. Essentially what i would like is a searchable date where they can filter items between two different dates Date raised or even just the one where you can say data raised >= 01/01/2014?
    Thanks
    jenn

    • Jenn says:

      in addition to this i would also like to know how i can transfer a only parts of a table rather than the entire table as mentioned in your code, i have tried specifing column ranges but it didnt seem to work.
      Sorry i am still new to VBA.
      Thanks heaps

  36. Monica says:

    Hi All.Thanks, Chandoo, for this awesome post. I have been searching high and low for a better way to parse through a lot of data, and this answers it. I do have two questions, though.

    1. When I execute the macro, the column widths on the destination page autofit to the data. Is there some way to prevent this?

    2. After I save the workbook as a macro-enabled workbook, I can no longer use the table features on my original raw data (the table button is greyed out). This is a huge bummer? Anyone run into a fix for this?

    Thanks!

    Monica

    • Hui... says:

      @Monica

      Q1. Comment out the line by adding a single quote
      'Columns.AutoFit

      Q2. Sounds odd, I haven't encountered that issue ?

      • Monica says:

        Ha! I didn't even notice the autofit line in the code! Thanks. Sometimes the answers to what seems like a very difficult challenge are very simple.

        I'm going to try to see if I can replicate issue number 2 in a new spreadsheet. Very strange!

        Thanks @Hui!

  37. Henry says:

    Hi, Thanks for this info!! Quick question, one of my filter parameters literally has about 10000 entries in it. It is quite tedicious to scroll 1 by 1. Is it possible to have a search within that filter so I won't have to scroll all the way through the filtered. (ie; if the first 2 letters are of the word "first", I would just like to enter in "fi" and then all the results with those first 2 letters should scroll to that portion automatically.

  38. Patrick says:

    If I want to filter using more than four categories, how do I do this?I've tried to add them using the demo file, but with no success.

  39. praveen says:

    I have a problem while using advanced filter.
    Based on the user's resonse,filter the table in the Activities sheet with an Advanced filter,to display only the activities matching the specified category.If the user doesn't enter one of the specified categories,then your macro must clear the filter(and the cell containing the filter criteria),so that all activities are displayed.Use a conditional Statement to achieve this.

  40. shpejtim says:

    how to create a total call duration and total up-sell at the last row in the same table after i press get data button thnx for answer 🙂

    • mongol says:

      after Columns.AutoFit add this code

      lastRow = ThisWorkbook.Sheets("Filter").Cells(Rows.Count, 7).End(xlUp).Row
      ThisWorkbook.Sheets("Filter").Range("g" & lastRow + 1) = _
      Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Filter").Range("g11:g" & lastRow))
      ThisWorkbook.Sheets("Filter").Range("g" & lastRow + 1).Font.Bold = True

  41. Jeff says:

    I'm having issues adding additional selection variables......how do I had a search box to go along with the 4 dropbox variables provided, where it's blank and I can simply enter a value, such as ">100"?

  42. mac says:

    please teach me on how to insert the function or codes on the "get data" button.
    thanks for any help.

  43. vadi says:

    Sub FilterExtract()

    Sheets("Filter").Select
    Range("A6").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("J1:L2"), CopyToRange:=Sheets("Filter").Range("A6"), Unique:=True

    Columns.AutoFit
    Sheets("Filter").Range("A6").Select
    End Sub

    I used the same Codes from sample macro but mine is extracting Header Row only. Any help please

  44. Hi,

    I have a range in which I have all Client Names.

    I would like to use Advance filter in VBA such that I am able to extract unique values of Client names as follows:

    All Client names that have "-ccs" as part of their name copied to a temp range and then

    All Client name that do not have "-ccs" as part of their name copied to a temp range.

    Any suggestions on how to setup criteria range to do this?

    Thanks.

    • Hui... says:

      @Thomas
      You can use an Advanced Filter but you will need to do it twice
      Once with the filter *-ccs
      Once with the filter LTGT*-ccs
      where LT is Shift , (Less than sign)
      and GT is Shift . (Greater than sign)

      The filter will simply be 2 cells
      Client Name
      *-ccs

  45. Ramesh says:

    Kudos Chandoo!!!! to create such a awesome Macro

    coming to my query I have large number of banks name around 1500 so dropdown won't looks good, is there any way to enter bank name manually and with partial name match also needs to work Example full bank name is database is "BANQUE MAROCAINE DU COMMERCE" by entering BANQUE should show the related rows in result. hence please help me with this regards.
    Tons of thanks in advance !!!! 🙂

  46. vinay mehta says:

    I have created a pivot table in excel 2010 and added a slicer . I want this pivot to place in shared workbook for everyone in my team to use .Please advise what's the way around placing the pivot table in shared workbook and slicer to work too.

  47. Hi Chandoo,
    I have problem regarding data Filter. From Below data i want to Filter the row which ends with L1,L2,L3. I have huge amount of data like this in which i need to extract the data based on different data character. I am using Filter (CTL+Shift+L) "Text Filter" filter with "ends With" but not able to do this. Please help
    DGS0105-A-L2
    DGS2006-A-L3
    DGS1006-A-S3
    DGS1006-A-S2
    DGS1506-A-S1
    DGS2006-A-L1
    DGS2006-A-D2
    DGS2006-A-D3
    DGS2006-A-D1

  48. projektowanie wn?trz Wrze?nia says:

    Very nice post. I just stumbled upon your blog and wished
    to mention that I've really loved surfing around your weblog posts.
    After all I'll be subscribing for your feed and I'm hoping you write once more
    soon!

  49. Warren says:

    Is anyone getting a "runtime error 9 - subscript out of range" error? Can't seem to figure out what I've done wrong

  50. Sung says:

    Hi, thanks for the great article. But I have a question and hope you can help me.

    Okay, using your example.

    Lets say I want to filter data but for both "Agent Bond and Mary" or "Agent Mary, Neo, Harry, and Smith"

    So it would mean that I am allowed to choose multiple values from the drop down list.

    The current code allows me to select one value per list.

    But I need a code that will allow me to select multiple values in one list

  51. jayanthi says:

    Hi friend,

    by watching the above url i am unable to extract the data.

    KIndly help me.

  52. Sergio says:

    This works just great, however I need to filter also my data from a long list in which my criteria is Account, Start Date and Last Date, My data includes Account and the date of the transaction. When I run the code, it filters the account but copies all the dates even though I have a couple of fields to select the first and last date. Could you help me out?

  53. JK says:

    Sheets("BOQ").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Sheets("MainMenu").Range("D1:E2"), CopyToRange:=Sheets("BOQTest").Range("A2"), Unique:=True

    Application-defined or object-defined error

  54. RPP says:

    Hi, you have just made an awesome macro. However, I need to put more than a single data per filter. For example, I need to chose Agent Bond and Mary at the same time.

    I tried to modify the line from 2 to 4:

    Sheets("#####").Range("P2:U4")

    but it doesn't work.

    Please, somebody help me!

  55. Huzefa says:

    Hello,

    I have made a Price list for inventory which uses this technique of filtering.
    I have three columns
    1. Brand
    2. Category
    3. Supplier.

    If I need the price of a product, I have to select the Brand, the Category and the Supplier from the drop down menu. That works fine.

    But, when I want the price of an entire category, I select category from the drop down and keep the remaining two (Brand and Supplier) blank. This does not work. I am forced to select an option from all the drop down menus. Can someone help me in fixing this issue? Thanks

  56. Carl says:

    What code? for 3 criteria but two then are date (filter between dates with another criteria).. same RawData ...? Thanks for your help

  57. Jeyanthi says:

    Hello, I am looking for this kind of filter for my task. But unable to follow your example. Can you pls. help me with step by step

    Thanks

  58. Aaron says:

    Hi All,

    I adapted the advanced filter code in the article for my work and I receive a run-time error "1004: application-defined or object-defined error".

    I have gone over all of my ranges and the syntax of the line. I am hoping someone here might be able to help me.

    Sheets("criteria").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= Sheets("criteria").Range(testArr(1)), CopyToRange:=Sheets("result").Range("A1"), Unique:=True

    The array I am using is being populated by the values selected in a combo box I created. I know the array is good because I can see the value in testArr (1).

    Feel free to reply with any questions you might have.

    Cheers,
    Aaron

    • Hui... says:

      @Aaron

      If the array stores a Range as Text you may want to try including that in an Indirect function
      Sheets("criteria").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= Sheets("criteria").Range(Indirect(testArr(1))), CopyToRange:=Sheets("result").Range("A1"), Unique:=True

      • Aaron says:

        Hui,

        What do you define this indirect function with? I get a compile error: sub or function not defined.

        Aaron

        • Hui... says:

          @Aaron
          Can you post the question in the Chandoo.org Forums and please attach a sample file
          http://forum.chandoo.org/

          • Aaron says:

            Its okay, I managed to solve my problem through trial and error.

            I set the sheets into a variable before the advanced filter. Then I inserted the variables in their respective placed. Here is some code for anybody who needs it.

            Dim criteria As Range
            Dim result As Range
            Dim wbData As Range

            Set criteria = Sheets("criteria").Range("G2")
            Set result = Sheets("result").Range("A1")
            Set wbData = Sheets("criteria").Range("A:E")

            wbData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
            criteria, CopyToRange:=result, Unique:=True

            Thanks anyways,

            Aaron

  59. Nick says:

    What is the macro code that is used for the "get data" button?

  60. Saibal Dutta says:

    Hi,

    I have excel sheet with data fields country, vendor name, PO number, PO amount, Invoice amount and PO status, under PO status if have multiple criteria like Open PO, Closed PO, Disputed PO. Based on above mentioned VB I have prepared the model and everything is working fine except a scenerio where I want "all" (OPEN, CLOSED & DISTPUTED) PO to be shown. For example

    Country - "India"
    Vendor Name - "Mahindra"
    PO Status - "All" (for all POs including "Open", "closed" & "Disputed")

    I can't leave PO status field blank because sometimes I may need only "Open" or "Closed" or "Disputed"

    Need help to resolve the issue.

    Thanks

  61. Frank says:

    This article has been very helpful.

    One challenge though. How do I set the 'copyto' sheet such that all possible filters based on the criteria set would all be displayed on the same sheet in manner such that they don't overlap.

    For instance, assuming the criteria is as follows: Agent Name, Product, Region.

    How can I get a different data set for filters that feature, say:
    1. Agent Vinod, Desktop, East
    2. Agent Vinod, Monitor, Midwest
    .
    .
    .
    to the very last combination possible. All on one excel sheet so that they are distinctly seperated (by a few blank rows) from previous combinations.

  62. Shirley Anne Laxamana says:

    Hi,

    Can you help me on how I can filter data with condition and automatically transfer it to another tab?

    For Ex. I have two columns for Comment and Date. On Date Column I will filter 11/17 date and it will return 2 data right? (the one with comment "add" and one with "add;late"). Then, I have a transfer button wherein when I click that, the data with "add" comment will go to sheet 1 and data with "add;late" comment will go to sheet 2.

    Comment Date
    add;late 11/17
    add 11/17
    add 11/18

    P.S. I just need the code for transferring as I already have the code for filtering. 🙂

    THANK YOU!

  63. princezz says:

    Ok,where on earth does this code go?

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

    How do I trigger the sheet to populate the data? 🙁

  64. Matt says:

    Is there a version of this that demonstrates how to select like Laptop and Monitor at the same time. I have seen many people ask but not a clear answer on this.

  65. Kiran Nalawade says:

    What if i have to copy only selected columns from the data and paste to another sheet? Is this possible? without VBA please.

  66. Josmar Fieror says:

    Hi,
    The above filter is great. However, I could not able to use your example because I'm working for multiple "raw data" in the workbook. What I did is i segregated the data every month into sheets. So, how should i work on this kind of filter. I was to reflect subtotal in every filter i will be getting.

    I hope someone here can able to help me.

    Thanks,
    Josmar

  67. Manoj says:

    Hi Sir, I have gone through your advance filter tutorial. i need some help on the same. can you advise. Below is the link of the sheet i am working on and the main sheet is sector details.

    https://www.dropbox.com/scl/fi/7d0qs8ni5w0bhecacx53k/Sector-New.xlsm?dl=0&rlkey=67lp8zp7xabcwfvc7xs7vc8e5

  68. Kanwaljit Singh Dhunna says:

    Hi Chandoo,

    How do I use Advance filter to extract data from a range which is sitting on a Closed Excel File ?

    How do I use Advance filter to extract data from a range which is sitting on a Closed Excel File on OneDrive ?

  69. Prabhakar says:

    I am facing problem in my excel wheet where i have written the VBA code for advance filter. Code is running fine but result are not correct. can anyone provide me the help.
    i want to upload my excel file here but i don't know how to upload my excel file here.

Leave a Reply