Excel VBA- Advanced Filter

In Excel VBA, using Auto filter is commonplace. But there are some limitations of it. We can overcome the limitations of autofilter by using advanced filter in VBA.

Advanced filter can be used by setting up criteria range. There must be at least two rows used for this.
The first row is replica of the fields of the table which are to be filtered and in the second row we write down the values or expressions. There can be more than two rows required for autofilter. Each row represents OR operator while each column represents AND operator.

The criteria range must be set on the input worksheet.

You can download the code file from below link:

Download Advanced Filter excel VBA file

Example:

Option Explicit

Sub MobilesAdvancedFilters()
    MobilesAF.Range("A:Z").Clear
    Mobiles.Range("A1").CurrentRegion.Copy MobilesAF.Range("E1")
    MobilesAF.Range("G1:G3").Copy MobilesAF.Range("A1")
    MobilesAF.Range("B2").Formula = "=I2*J2<40000"
    MobilesAF.Range("B3").Formula = "=I2*J2<40000"
    MobilesAF.Range("E1").CurrentRegion.AdvancedFilter xlFilterCopy, MobilesAF.Range("A1:B3"), MobilesAF.Range("M1")
End Sub