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 SubExcel 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.
Real-time Applications on Excel VBA Range Advanced Filters
VBA MsgBox Function
The VBA MsgBox function is used to display a message box that contains a specific message and asks the user to click a button to continue. It is commonly used in VBA programs for user [...]
VBA StrConv Function
The StrConv function in VBA (Visual Basic for Applications) is used to convert a given string into a specified case. It is a built-in function that is used to manipulate strings in Excel. The term [...]
VBA Split Function
The VBA Split function is a string function that is used to divide a text string into an array of substrings. These substrings are separated by a specified delimiter, such as a comma or space. [...]
VBA Space Function
The VBA Space function is used to generate a string of spaces, with the specified number of spaces as the argument. This function is useful for formatting data and creating space between text or numbers [...]
VBA Exp Function
The VBA Exp function is a mathematical function that calculates the exponential value of a given number. In simpler terms, it calculates the value of 'e' raised to the power of the specified number. 'e' [...]
VBA Abs Function
The VBA Abs function is an essential mathematical function that is used to return the absolute (or positive) value of a number. It means that the function always returns a non-negative number, regardless of the [...]
VBA Dir Function
VBA Dir Function - Purpose, Syntax and Arguments Description The VBA Dir function is a built-in function in Microsoft Excel and other MS Office applications that returns the name of a file, folder, or directory [...]
VBA FileAttr Function
The VBA FileAttr function is used to retrieve certain attributes of a specified file. These attributes can include information such as the file's name, size, or date created. This function is similar to the File [...]
VBA DateAdd Function
VBA DateAdd Function - Purpose, Syntax and Arguments Description: The VBA DateAdd function is a built-in function that is used to manipulate date and time values in Microsoft Excel. It is a very useful and [...]
VBA CDec Function
The CDec function in VBA stands for "Convert to Decimal" and is used to convert a given expression or value into a decimal data type. This function is useful when dealing with different data types, [...]