Advanced Filter Excel VBA (Visual Basic for Applications)

Advanced Filter Excel VBA (Visual Basic for Applications)

Advance Filter

Excel Range object provides advanced filter method. By putting advanced filter, user can show specific data in the data set or sheet. It is very useful excel feature which allows user to narrow-down search in bulk data set. Advanced filter having certain parameters which must be supplied while accessing through code.


expression.AdvancedFilter(Action, CriteriaRange,CopyToRange,Unique)

List of Parameters

  • Action (Required): One of the constants of XlFilterAction specifying whether to make a copy or filter the list in place.
  • CriteriaRange (Optional): The criteria range. If this argument is omitted, there are no criteria.
  • CopyToRange (Optional): The destination range for the copied rows if Action is xlFilterCopy . Otherwise, this argument is ignored.
  • Unique (Optional)True to filter unique records only. False to filter all records that meet the criteria. The default value is False .

Code example

Public Sub ApplyFilter()
	Sheets("Sheetname").Range("A1:Z2000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Sheetname").Range("F1:F2"), CopyToRange:=Range("A1:D1"), Unique:=False
End Sub

Data source

Code example

Public Sub AdvancedFilterExample()
    'Declare range object
    Dim oRange As Range
    'bind selection
    Set oRange = Selection
    'Variable to target range
    Dim oTargetRange As Range
    'bind target range
    Set oTargetRange = ActiveSheet.Range("C1")
    'apply filter to get unique values
    oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oTargetRange, Unique:=True
    'Memory cleanup
    Set oRange = Nothing
    Set oTargetRange = Nothing
End Sub


Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next : Current Region in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *