Advanced filters are very powerful and useful while dealing or filtering the data with verity of conditions. We can apply Advanced Filters using VBA. We can copy the data into another location or we ca filter the data in the same location.
Excel VBA Range Advanced Filter – Syntax
Here is the syntax or VBA code to apply the advanced filter in a range of data.
Range(“YourRange”).AdvancedFilter(Action As XlFilterAction,[CriteriaRange], [CopyToRange], [Unique])
- Here AdvancedFilter is a Member of Excel.Range Object.
- XlFilterAction: You can specify how you want perform the filter:
- Const xlFilterInPlace = 1 will filter in the range data source.
- Const xlFilterCopy = 2 will filter the data and copy to the target range, in this case you have to provide the [CopyToRange] which you want to copy the filtered data.
- [CriteriaRange]: You can specify the filter criteria range where you can mention any criteria including formulas.
- [Unique]: You can set to TRUE if you want to get unique records, False to set all records.
Excel VBA Range Advanced Filter- Examples
Here is the simple example to apply the advanced filer in a range. Assuming that you have data in Range “A3:B8” and You have specifies the criteria at Range “B1:B2”. Here we are using xlFilterInPlace to filter the data in the data range itself. and no need to provide the target range.Sub VBA_Range_Advanced_Filter() Range("A3:B8").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Range("B1:B2"), Unique:=False End Sub
Excel VBA Range Advanced Filter- Instructions
Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
- Open an Excel Workbook from your start menu or type Excel in your run command
- Now you have to prepare some sample data to test this macro.
Enter the header as SerialNumber at A3 and Value at B3)
- Enter some sample data from range A4 to B8, say 1 to 5 in SerialNumber and 100 to 500 in values fileld
- Now enter the criteria, I am entering at ‘SerialNumber’ at Range B1 and ‘>2’ as criteria at Range B2. So your criteria range will be “B1:B2”
- Press Alt+F11 to Open VBA Editor or you can goto Developer Table from Excel Ribbon and click on the Visual Basic Command to launch the VBA Editor
- Insert a Module from Insert Menu of VBA
- Copy the above code (for filtering the data in a range using Excel VBA) and Paste in the code window(VBA Editor)
- Save the file as Macro Enabled Workbook (i.e; .xlsm file format)
- Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line.
Now, You can observe that the records are displaying only for values 3,4 and 5.PREMIUM TEMPLATES LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Ultimate Project Management Template
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
Real-time Applications on Excel VBA Range Advanced Filters
VBA ActiveSheet Object helps to refer the currently activated sheet in the active workbook. We can get all properties and methods of the ActiveSheet using VBA. Let us see the useful of the examples on
VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set
Very often we need to copy the Excel Range to another sheet with formatting. We can use VBA to automate this task. Excel VBA Copy Range to Another Sheet with Formatting macro is explained to
VBA to filter Multiple Columns code helps applying the filters in multiple columns. Let us see the example macros to filter the records or rows based on items in multiple columns using VBA. Example Data
VBA filter column macro helps filter the data in a specific Column. Let us see different example macros to filter the records or rows based on items in one column. Example Data to Explain the
VBA Filter function is very useful for filtering the records to suit our requirement. We use VBA Filter function to filter the records with verity of criteria to get the limited number of records. Sometimes
Free Invoice Template Excel Format helps you to quickly prepare your invoice using Microsoft Excel. Download Free Invoice Template in Excel Format. Template includes Total, Subtotal, Tax, VAT, Discount and Amount Due with Formula to
Hide Developer Tab in Excel Ribbon using from the Excel options. It helps to prevent users from opening the Excel VBA editor window. Hide Developer Tab in Excel Ribbon: Right click on any main menu
Show or hide means to display or hide a Userform. We use Show and Hide methods to display or hide. Unload will be used when completes the task.
Column Number to Column Name is nothing but converting column number to Excel alphabetic character column name. Most of the time while automating many tasks using Excel VBA, it may be required.
Related ResourceExternal VBA Reference
Excel VBA Reference Project Management Reference VBA Reference:
- VBA Code Explorer
- VBA Excel Application
- VBA Excel Workbook
- VBA Excel Worksheet
- VBA Excel Range
- VBA ActiveX Controls
- VBA Userforms
- VBA Projects
- What is a Project?
- Project Appraisal
- Project Management
- Project Plan
- Project Resource
- What is Gantt Chart?
- Excel Templates
- Excel Project Management Templates
- PowerPoint Project Management Templates
- MS Word Project Management Templates