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.
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:
- Split an excel file in to many using Advanced Filters & VBA [with video]
- Move data from one sheet to many using VBA
- Split text on new line using VBA
- Consolidate data from multiple files in to one using VBA Macros
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.
126 Responses to “Extract data using Advanced Filter and VBA”
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]
In the VBA code just add some code to format to how you want your new formatting to be.
For some examples look here:
http://msdn.microsoft.com/en-us/library/office/aa224873(v=office.11).aspx
http://msdn.microsoft.com/en-us/library/office/aa173167(v=office.11).aspx
hi
the above advanced filter is great, but if i want one criteria to list all instead of particular data what should i do.. let me say i want all details of product sales how do i filter it , rest of criteria should be all no agent id name or no region or no customer type
@Sriram
To select all records matching a field Simply leave that field blank, ie: Unfiltered
I tried by adding a filed blank by extending the range of the data filter (name range) not working.. any idea pls
it workign when i use this formula "=IF(Filter!C8,"","")" in raw data sheet instead of "=Filter!C8", because the value is taken as 0 if we do not use if condition
i have another doubt how do we give criteria in a specified range in a separate sheet, not like the above giving in the same range... is it possible
@Sriram
Ask the question in the Forums and attach a sample worksheet
http://chandoo.org/forum/
Hey Sriram,
Did you figure this out?
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.
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?
“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.
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
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.?
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.
Hi Kdu - Not sure what you mean and where you would use the =, <>, and blank...
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.
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
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.
Sorry, there is a typing error.
Please replace Range("B10?) by Range("B10").
Kdu.
Or just
Sheets("Filter").Range("B10").CurrentRegion.Clear
This will also clear the headers line. Thats why I used the .Resize function.
Kdu.
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
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.
Why we need elaborate coding?
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
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.
@Jon
You've misread, Mike H's post
Mike H suggested exactly what you said and offered an alternative using the Current region modifier
@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!
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.
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
@CMC,
That does it! It removes all old data. Pretty nice. I'll have to keep that one in mind.
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....
@Sunil,
You may want to refer to a previous article to accomplish this.
http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/
~VijaySharma
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?
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??
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>
@RichW,
Correction:
Per Microsoft you can copy to another sheet without VBA.
http://support.microsoft.com/kb/117643
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.
@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>
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
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
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"
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
Hi How do I define the range Filter!C5:C8 to Filters.All. Any help would be great.
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
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
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
Hi Abbasi ,
You can mail me your file at narayank1026(at)gmail(dot)com
Narayan
Thanks Narayan,
I have just sent you an email with attachment.
Regards
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.
[...] Extract data using Advanced Filter and VBA [...]
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
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.
this filter is simply, great...this tool i use everyday in my macros....
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?
Hi Chandoo,
I have studied your artcle about Advanced Filter. Your have explained very understandble way. You have a wonderful teaching capacity.
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!
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!
I would also like to know please
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
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?
I have the same question as aaron, how can I copy only certain columns instead of all the table?
[…] 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 […]
[…] 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 […]
[…] http://chandoo.org/wp/2012/11/27/extract-subset-of-data/ […]
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.
yes i am looking at doing the same thing 🙁 help please
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"
You can use 2 conditions. See here for advanced filter help.
http://chandoo.org/wp/2011/10/10/how-to-use-advanced-filters/
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
Could you please elaborate
"The named ranges were created using the INDEX function as shown below"
Novice here!!!
Me too!
What do you mean by:
“The named ranges were created using the INDEX function as shown below”
greetings!!!
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.
i just found why this wasnt working. my table name didnt match. sorry to waste your time.
Thanks again love your work
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
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
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
@Monica
Q1. Comment out the line by adding a single quote
'Columns.AutoFit
Q2. Sounds odd, I haven't encountered that issue ?
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!
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.
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.
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.
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 🙂
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
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"?
please teach me on how to insert the function or codes on the "get data" button.
thanks for any help.
Click "Insert" menu, Click Shapes, Select any shape as desired, Right click the shape, click "Assign macro" then select the macro name
thanks vadi 🙂
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
The code is working now.
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.
@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
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 !!!! 🙂
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.
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
@Ranveer
Why not just add afield which extracts the last two characters
eg: =Right(A2,2)
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!
Is anyone getting a "runtime error 9 - subscript out of range" error? Can't seem to figure out what I've done wrong
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
Hi friend,
by watching the above url i am unable to extract the data.
KIndly help me.
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?
@Sergio
Can you post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to simplify the solution
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
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!
@RPP
Can you please ask the question at the Chandoo.org Forums
Please attach a sample file to simplify the response/answer
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
What code? for 3 criteria but two then are date (filter between dates with another criteria).. same RawData ...? Thanks for your help
Hey Carl,
Was that response for me?
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
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
@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
Hui,
What do you define this indirect function with? I get a compile error: sub or function not defined.
Aaron
@Aaron
Can you post the question in the Chandoo.org Forums and please attach a sample file
http://forum.chandoo.org/
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
What is the macro code that is used for the "get data" button?
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
@Saibal
Can you please ask the question at the Chandoo.org Forums
http://chandoo.org/forum/
Please attach a sample file to get a more targeted response
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.
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!
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? 🙁
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.
What if i have to copy only selected columns from the data and paste to another sheet? Is this possible? without VBA please.
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
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
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 ?
Hey Kanwaljit...
You cannot use filters on closed files. For such situations, you are better off using Power Query. - See here for a quick intro - Powerful Introduction to Power Query
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.