The List(or data) range is shaded blue, the Criteria range is green, and the CopyTo range is yellow: The following screenshot shows an example of the 3 ranges. RgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination, True RgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange,, True ' Filter and copy data Otherwise, duplicate records are ignored: ' Filter in place To remove duplicate records we simply set the Unique parameter to True. If we use xlFilterInPlace then we don’t need the destination range. xlFilterCopy – Copy the filter results to a new range.xlFilterInPlace – Filter the original data.The first parameter indicates the way to apply the filter: RgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination RgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange This means there are two ways to use AdvancedFilter: ' Filter in place We can filter in place or we can copy the filter results to another location. This means you get the range of data you wish to filter and then call the AdvancedFilter function of that range: DataRange.AdvancedFilter Filter Action, Criteria,, Copy To range – where to place the results if the Action parameter is set to xlFilterCopy is set.ĪdvancedFilter is a range Function.You can read about the parameters on the Microsoft help page.ĪdvancedFilter requires three ranges to run(or two if you are using xlFilterInPlace as the Action parameter): Range of the criteria used for filtering the data.ĭestination range if Action is set to xlFilterCopy. The following table shows the parameters of the AdvancedFilter function: To see me working with Advanced Filter, check out this YouTube video: Using Advanced Filter is very useful in VBA because it is extremely fast, powerful and as we will see it requires very little code. If we decided to copy to another location then we provide the “Copy to” range. We then simply need the data range(List range) and the Criteria Range. We filter in-place or we copy to another location. Using Advanced Filter is quite simple as you can see from the dialog: This allows us to do more advanced filtering than the standard filtering on the worksheet.Ī second advantage of using Advanced Filter is that we have the option to copy the results to a new range if we choose. It allows us to filter data based on a Criteria range. You don’t need to include a column header in the criteria if you are not filtering by this column.Īdvanced Filter is a tool that is available in the Sort & Filter section of the Data tab on the Excel Ribbon:.You can use the same header multiple times(see section Advanced Filter Multiple Criteria below).Here are some important things to know about the Criteria column headers: For example, if the Criteria column header is “Fruit” then there must be a List range column header called “Fruit”. Important Note: A Criteria column header must exist as a List range column header. Pear, Peas or any 4 letter word starting with "Pea"Ĭase sensitive(see section Using Formulas as Criteria) Use the ? symbol to represent any single character You can see the possible options in the table below:
Using the criteria with AdvancedFilter is very powerful.
0 kommentar(er)
