REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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.
VBA AdvancedFilter Method Excel Range Object

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:

    1. Open an Excel Workbook from your start menu or type Excel in your run command
    2. 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 Error Statement

VBA Error Statement

Error handling plays a pivotal role in ensuring smooth and predictable code execution. While VBA provides mechanisms to handle errors, there are instances where simulating errors becomes necessary. Enter the 'Error' statement. In this blog [...]

  • VBA Date Function

VBA Date Function

The VBA Date function is a built-in function that is used to return the current system date. It can also be used to specify a specific date by providing the year, month, and day arguments. [...]

  • VBA Time Function

VBA Time Function

The VBA Time function is used to return the current system time. It is similar to the 'Now' function, but it only returns the time and not the date. This function can be very useful [...]

  • VBA String Function

VBA String Function

The VBA String function is a built-in function in Microsoft Excel that allows users to create a string, or a sequence of characters, of a specified length. This function is commonly used in VBA (Visual [...]

  • VBA Seek Function

VBA Seek Function

While automating file operations within VBA (Visual Basic for Applications), understanding where you are (i.e., your position within a file) is crucial, especially when reading or writing data. The VBA Seek function serves as a [...]

  • VBA Mid Function

VBA Mid Function

VBA Mid function is a string manipulation function that allows users to extract a specific number of characters or a portion of a string from a given text based on a starting position and a [...]

  • VBA Input Function

VBA Input Function

The VBA Input function reads a specified number of characters from an open sequential file and returns the result as a string. The VBA Input function is a file handling tool that reads a defined number [...]

  • VBA MsgBox Function

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

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

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. [...]

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates