A Few Words About the AdvancedFilter Method with Excel VBA

The AdvancedFilter method for the Range object allows you to programmatically perform advanced filtering of a list according to criteria specified in the parameters:

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

• expression — a reference to a cell in the range or to the range itself that will be filtered.
• Action — a constant that specifies whether to keep the filtered data on the worksheet (xlFilterInPlace) or copy it elsewhere (xlFilterCopy).
• CriteriaRange — optional parameter specifying the criteria range; if omitted, no criteria are applied.
• CopyToRange — optional parameter specifying the range where the filtered data will be copied if xlFilterCopy is chosen.
• Unique — parameter specifying whether only unique values should remain in the filtered range. Accepts values True or False; the default is False.

Demonstration Example

Suppose the worksheet contains weather data. The task is to determine cities:

• where air pressure is greater than the maximum value for the city of Grodno, or
• where precipitation is either rain or snow, and its amount exceeds the average for all types of precipitation by no more than 23%.

Steps:

• Open the list to be filtered (range A1:I49, header row A1:I1, worksheet Precipitation).
• Prepare the calculated criterion for the Advanced Filter in the range L1:L2 (Fig. 7.15).
  • Cell L1 contains the word Condition.
  • Cell L2 contains the formula:

=OR(AND(C2<>"Grodno",G2>MAX($G$26:$G$33)),
    AND(OR(D2="rain",D2="snow"),
        OR(AND((E2-AVERAGEIF($D$2:$D$49,"rain",$E$2:$E$49))/AVERAGEIF($D$2:$D$49,"rain",$E$2:$E$49)*100>0,
               (E2-AVERAGEIF($D$2:$D$49,"rain",$E$2:$E$49))/AVERAGEIF($D$2:$D$49,"rain",$E$2:$E$49)*100<23),
           AND((E2-AVERAGEIF($D$2:$D$49,"snow",$E$2:$E$49))/AVERAGEIF($D$2:$D$49,"snow",$E$2:$E$49)*100>0,
               (E2-AVERAGEIF($D$2:$D$49,"snow",$E$2:$E$49))/AVERAGEIF($D$2:$D$49,"snow",$E$2:$E$49)*100<23))))

• Place two CommandButton controls on the worksheet. One button will perform data filtering, the other will remove the filter.
• Set the Caption property for the first button (CommandButton1) to Filter, and for the second (CommandButton2) to Remove Filter.
• Enter the program code shown in Listing 1 in a standard module, and the code in Listing 2 in the Sheet1 module.

Listing 1. Advanced Filtering — Standard Module

Sub AdFilt()
    Range("A1:I49").AdvancedFilter Action:=xlFilterInPlace, _
                                   CriteriaRange:=Range("L1:L2"), Unique:=True
End Sub

Sub Del()
    Worksheets("Precipitation").ShowAllData
End Sub

Listing 2. Advanced Filtering — Sheet1 Module

Private Sub CommandButton1_Click()
    AdFilt
End Sub

Private Sub CommandButton2_Click()
    Del
End Sub

• Now, to obtain the filtered data according to the criteria, simply click the Filter button located on the Precipitation worksheet. To remove the filter, click the Remove Filter button.