Étiquette : processing_lists

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.

The Advanced Filter provides the user with more options for specifying selection criteria, taking into account AND, OR, and calculated criteria. Searching with the Advanced Filter is carried out according to the following guidelines:

• Prepare the criteria range for the Advanced Filter:
  • The top row contains the field headers on which the selection will be based (these must exactly match the field headers of the list).
  • The search criteria are entered into the empty rows below the prepared header row. Keep in mind:
    • To apply an AND condition, place the criteria side by side in the same row.
    • To apply an OR condition, place the criteria on different rows.
    • To use a calculated criterion, include formulas (custom or Excel functions) whose arguments are fields of the list.
      • The calculated criterion is placed under a custom header (for example, Condition), which must not match any existing field name of the list.
      • Relative references are used for list fields, pointing to the top record in the list’s data range.
      • References to cells outside the list must be absolute.
      • The result of evaluating the criterion must be a logical value (TRUE or FALSE). The Advanced Filter selects records where the result is TRUE.
    • In the case of complex conditions, the search is carried out using composite criteria with AND and OR. The criterion should be constructed with logical functions AND(), OR(), NOT().
• Place the cell pointer in the list (select the entire required list or a part of its range).
• Go to the Data tab on the ribbon, and in the Sort & Filter group, click the Advanced button. In the Advanced Filter dialog box:
  • Specify the location where the filtered results should be placed.
  • In the List range field, mark the entire list to be filtered (if the pointer was placed in the list, this range is selected by default).
  • In the Criteria range field, specify the prepared criteria range (you can select it directly on the worksheet with the mouse).
  • If the filtered records need to be copied elsewhere, specify the Copy to range.
  • To filter out only unique records (without duplicates), select the checkbox Unique records only.

The results of the Advanced Filter are displayed on the same worksheet or copied to another location.

Examples

Example 1. Determine if the list contains yellow or black cars, manufactured after 2003, priced between 2,500 and 15,000 USD, or beige Mercedes cars with mileage greater than 20,000 km but less than 100,000 km.

• Open the list to be filtered (range A1:J58, header row A1:J1, worksheet Cars_List-1).
• Prepare the criteria range for the Advanced Filter.

• Go to the Data tab, in the Sort & Filter group, click Advanced.
• In the Advanced Filter dialog box, specify the required ranges and filter parameters. Click OK.

Example 2. Determine if the list contains cars manufactured after 2000 with mileage greater than 10,000 km but less than 100,000 km, or black Mercedes cars priced between 20,000 and 30,000 USD.

• Open the list to be filtered (range A1:J133, header row A1:J1, worksheet Cars_List-2).
• Prepare the calculated criterion for the Advanced Filter in the range M3:M4.
  • Cell M3: enter the word Condition.
  • Cell M4: enter the formula:

=OR(AND(G2>10000,G2<100000,D2>2000),AND(C2="Mercedes",F2="black",H2>20000,H2<30000))

• On the Data tab, in the Sort & Filter group, click Advanced.
• In the Advanced Filter dialog box, specify the required ranges and filter parameters. Click OK.
• The filtered data will be displayed on the worksheet.

Example 3. Determine white or red cars whose price is lower than the average price of all cars, and whose mileage is greater than or equal to the average mileage.

• Open the list to be filtered (range A1:J133, header row A1:J1, worksheet Cars_List-3).
• Prepare the calculated criterion for the Advanced Filter in the range L1:L2:
  • Cell L1: enter the word Condition.
  • Cell L2: enter the formula:

=AND(OR(F2="white",F2="red"),H2<AVERAGE($H$2:$H$133),G2>=AVERAGE($G$2:$G$133))

• On the Data tab, in the Sort & Filter group, click Advanced.

Let’s consider the creation of an application that filters data for a specified column in a data list.

First, create a workbook and rename Sheet1 to List. Delete the remaining worksheets of the workbook. Place the prepared table of data about cars and their owners on the List sheet.

When you right-click the table header, a context menu should appear with a single command: Filter. Selecting this command should call a dialog box asking for the address of the header cell of the data list. After specifying the cell address, a form should appear with a list box containing the values from that column and a check box labeled Filter.

• If the check box is selected, the data on the worksheet will be filtered by the value chosen in the list.
• If the check box is cleared, the filter will be removed.

Now, create a form and place on it a ListBox and a CheckBox. Using the Properties window, set the Name property of the form to frmFilter.

Enter the corresponding code in the form module, in the ThisWorkbook module, and in a standard module.

In the Form Module

There are three procedures:

• Form Initialize event procedure — sets the Caption properties of the check box and form, and fills the list. The list is filled with values from the column of the table whose first cell address is entered by the user. Duplicate values are skipped during filling.
• ListBox Change event procedure — when an item is selected in the list, if the Filter check box is selected, the table is filtered. If AutoFilter arrows have not yet been displayed in the table, this procedure first initializes AutoFilter and then performs filtering.
• CheckBox Change event procedure — depending on the state of the check box, either performs filtering (by calling the list box Change event procedure) or removes AutoFilter.

In the ThisWorkbook Module

There are three procedures:

• Workbook Open event procedure — creates a context menu with a single item Filter, linked to the procedure DoFilter.
• Workbook SheetBeforeRightClick event procedure — displays the context menu when the user right-clicks the first row of the List sheet.
• Workbook BeforeClose event procedure — deletes the context menu from the workbook when the workbook is closed.

In the Standard Module

There is a single procedure DoFilter, which displays the Field_Name window.

The AutoFilter method of the Range object allows you to programmatically apply AutoFilter to a list according to criteria specified in its parameters:

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

• expression — a reference to a cell in the range or to the range itself that will be filtered.
• Field — optional parameter specifying the number of the field (column) in which data will be filtered. Numbering starts from the leftmost field, where the first field has the number 1.
• Criteria1 and Criteria2 — optional parameters specifying up to two criteria for filtering the field. You can use a string constant, for example « 101 », and relational operators >, <, >=, <=, =, <>. Use = to filter empty fields and <> to filter non-empty fields. If the parameter is omitted, the criterion is All. If the parameter Operator equals xlTop10Items, then Criteria1 defines the number of displayed items (for example, « 30 »).
• Operator — optional parameter. Valid values are the following XlAutoFilterOperator constants:
  • xlAnd (logical AND of the first and second filter criteria),
  • xlOr (logical OR of the first and second criteria),
  • xlTop10Items (displays the top 10 items of the field),
  • xlBottom10Items (displays the bottom 10 items of the field),
  • xlTop10Percent (displays the top 10% of items),
  • xlBottom10Percent (displays the bottom 10% of items).
• VisibleDropDown — optional parameter (Boolean). Determines whether drop-down arrows are shown. Default is True.

When writing VBA programs that use AutoFilter, the following methods and properties are also helpful:

• The AutoFilter method of the Range object, used without parameters, displays or hides the AutoFilter arrows.
• The ShowAllData method of the Worksheet object displays all data in the list—both filtered and unfiltered. Example:
• Worksheets(« Orders »).ShowAllData
• The AutoFilterMode (read-only) property of the Worksheet object returns True if AutoFilter arrows are present on the worksheet.
• The FilterMode (read-only) property of the Worksheet object returns True if filtered data with hidden rows exists on the worksheet.

The AutoFilter object encapsulates the data about the AutoFilter used on a worksheet. This object is returned by the AutoFilter property of the Worksheet object.

Table. Main properties of the AutoFilter object

Property	Description
Filters	Returns a collection of Filter objects, i.e., all filters that form the given AutoFilter. The properties of the Filter object are given in Table 7.2. For the Filters collection, the main properties are Count and Item, which return the number of items and a specific item in the collection.
Range	Returns the range to which the filter is applied.

Table. Properties of the Filter object

Property	Description
On	Returns True if the filter is applied.
Criteria1	Returns the first filter criterion.
Criteria2	Returns the second filter criterion.
Operator	Returns the filter operator. Valid values are the XlAutoFilterOperator constants.

Note also that the EnableAutoFilter property of the worksheet allows you to control access to AutoFilter drop-down lists on a protected sheet. If its value is True, the AutoFilter drop-down lists are available to the user even when the worksheet is protected. In this case, protection must be enabled for the user interface only:

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True

As an example, consider a procedure using the AutoFilter object. In the standard module, place the procedure code that first determines whether an AutoFilter exists on the worksheet, and if so, retrieves the total number of filters and the filter criteria for the applied custom AutoFilters. In the Sheet1 module, place the code that handles the button click.

If you use AutoFilter to search for data in a list, arrows appear to the right of the column headers in the filtered list. For filtered data, the row numbers are highlighted in blue, and the AutoFilter arrow changes into a funnel (filter) symbol next to the fields by which the data has been filtered.

In the AutoFilter drop-down list you can:

• Sort data by selecting Sort Smallest to Largest or Sort Largest to Smallest.
• Sort data by color, using the Sort by Color command; in the drop-down list next to this command, you can define a custom sort order.
• Remove a filter from a field, using the command Clear Filter From « Field_Name ».
• Apply a filter by color, using the Filter by Color command.
• Specify a numeric range for filtering data, using Number Filters. In the drop-down list next to this command, you can select Custom Filter, which opens the Custom AutoFilter dialog box, where a simple filter criterion with up to two conditions can be set.
• Search for data using the Search box by entering a criterion for exact match or approximate match using a pattern.
• Select a field value for an exact match search.

Searching with AutoFilter is done in the following order:

• Place the cell pointer within the data list.
• Go to the Home tab of the ribbon, in the Editing group click the Sort & Filter button and select Filter, or use the Filter command located in the Sort & Filter group on the Data tab of the ribbon. AutoFilter arrows will appear next to each field in the header row of the list.
• Go to the required field.
• Choose the desired search criterion or use the Custom AutoFilter. Note that the Custom AutoFilter dialog box allows you to quickly set more complex conditions than a simple comparison. In the top-left drop-down list, select a comparison operator (in this case, greater than or equal to), and in the right-hand field, enter or select a value (here: 9000). Then, if necessary, choose either the AND or OR operator, set the second comparison operator and value. In this example, the AND option is chosen, the operator less than, and the value 20000. Finally, click OK to obtain the result of the custom filter.
• To include another field in the criterion, return to step 3.

In Microsoft Office Excel 2010, by using the Sort dialog box, you can specify in sequence how and by which columns the data should be sorted. However, if you need to sort the entire list in ascending or descending order, taking into account all the columns that belong to the list, follow the example below.

• First, place a list with data on the worksheet. For example, let’s again use our list of car owners.
• Add two CommandButton controls. The first button will perform ascending sorting for all columns of the list, and the second button will perform descending sorting.

• Change the Caption property values of the buttons respectively to:
  • SORT ASCENDING FOR ALL COLUMNS
  • SORT DESCENDING FOR ALL COLUMNS
• Add the program code that performs the sorting to a standard module, and the program code that handles the button clicks to the Sheet1 module.

Now let’s look at an example of sorting list data, but only for a pre-selected range of the list. As source data, we again use a table of cars and their owners.

On the worksheet, add a CommandButton control, and change its Caption property value to:
SORT ALL COLUMNS FOR THE SELECTED RANGE OF THE LIST.

Sorting list data by a selected range. Standard module

Sub SortColumnUp()
    Dim k As Long
    For k = Selection.Columns.Count To 1 Step -1
        Selection.Sort Key1:=Selection.Cells(2, k), Order1:=xlAscending, _
                       Header:=xlGuess, Orientation:=xlTopToBottom
    Next k
End Sub

Sorting list data by a selected range. Sheet1 module

Private Sub CommandButton1_Click()
    SortColumnUp
End Sub

Data on a protected sheet can be sorted if they are within a range where the Locked property is set to False, and if the AllowSorting parameter of the Protect method is set to True.

The read-only property AllowSorting of the Protection object returns the value of this parameter. For example, the code allows sorting the range A1:A5 on a protected sheet, if it has not been enabled previously.

Sorting on a Protected Sheet

Sub DemoAllowSorting()
    ActiveSheet.Unprotect
    Range("A1:A5").Locked = False
    If ActiveSheet.Protection.AllowSorting Then
        ActiveSheet.Protect AllowSorting:=True
    End If
End Sub

Let’s consider an example that demonstrates the use of the Sort method. Suppose a worksheet contains a list of data about cars and their owners. Place two CommandButton controls on the worksheet: one will perform ascending sorting by three arbitrary columns, and the other will perform descending sorting.

Change the Caption property values accordingly: for the first button (CommandButton1) set SORT ASCENDING, and for the second button (CommandButton2) set SORT DESCENDING.

Sorting by three fields in ascending and descending order. Standard module

Sub Sort_Up()
    Range("A1").Select
    x = InputBox("Enter the column address for sorting by the first field", _
                 "Enter range")
    y = InputBox("Enter the column address for sorting by the second field", _
                 "Enter range")
    z = InputBox("Enter the column address for sorting by the third field", _
                 "Enter range")
    Selection.Sort Key1:=Range(x), Order1:=xlAscending, _
                   Key2:=Range(y), Order2:=xlAscending, _
                   Key3:=Range(z), Order3:=xlAscending, Header:=xlYes
    Range("A1").Select
End Sub

Sub Sort_Down()
    Range("A1").Select
    x = InputBox("Enter the column address for sorting by the first field", _
                 "Enter range")
    y = InputBox("Enter the column address for sorting by the second field", _
                 "Enter range")
    z = InputBox("Enter the column address for sorting by the third field", _
                 "Enter range")
    Selection.Sort Key1:=Range(x), Order1:=xlDescending, _
                   Key2:=Range(y), Order2:=xlDescending, _
                   Key3:=Range(z), Order3:=xlDescending, Header:=xlYes
    Range("A1").Select
End Sub

Sorting by three fields in ascending and descending order. Sheet1 module

Private Sub CommandButton1_Click()
    Sort_Up
End Sub

Private Sub CommandButton2_Click()
    Sort_Down
End Sub

Now let’s look at several examples related to sorting data using VBA programs.

To sort data by up to three criteria, the Sort method is applied. This method allows you to sort rows in lists, pivot tables, and databases, as well as columns in worksheets:

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, _
OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, _
DataOption2, DataOption3)

• expression — a reference to a cell range or to the range itself that will be sorted.
• Key1 — optional parameter that specifies the reference to the first field to sort.
• Order1 — optional parameter that determines the sort order for the field specified by Key1. Valid values are the following XlSortOrder constants:
  • xlAscending (ascending order),
  • xlDescending (descending order).
• Key2 — optional parameter that specifies the reference to the second field to sort.
• Type — optional parameter that specifies the elements that should be sorted. Used only with PivotTables.
• Order2 — optional parameter that determines the sort order for the field specified by Key2. Valid values are the XlSortOrder constants.
• Key3 — optional parameter that specifies the reference to the third field to sort.
• Order3 — optional parameter that determines the sort order for the field specified by Key3. Valid values are the XlSortOrder constants.
• Header — optional parameter that specifies whether the first row of the list contains headers. Valid values are the following XlYesNoGuess constants:
  • xlYes (the first row of the range contains a header, which is not sorted),
  • xlNo (the first row of the range does not contain a header, default value),
  • xlGuess (MS Excel decides whether the list has a header).
• OrderCustom — optional parameter that specifies a custom sort order. It is an integer indicating the index number of the list used as a sorting template.
• MatchCase — optional parameter that indicates whether to consider case sensitivity when sorting.
• Orientation — optional parameter that specifies the orientation of the sort. Valid values are the following XlSortOrientation constants:
  • xlTopToBottom (sorting is performed top to bottom, i.e., by rows),
  • xlLeftToRight (sorting is performed left to right, i.e., by columns).
• SortMethod — optional parameter that specifies the sorting method. Used for languages such as Chinese and Japanese.
• DataOption1 — optional parameter that specifies how text should be sorted in the field defined by Key1. Valid values are the following XlSortDataOption constants:
  • xlSortTextAsNumbers (numeric and text data are sorted together),
  • xlSortNormal (numeric and text data are sorted separately).
• DataOption2 — optional parameter that specifies how text should be sorted in the field defined by Key2. Valid values are the XlSortDataOption constants.
• DataOption3 — optional parameter that specifies how text should be sorted in the field defined by Key3. Valid values are the XlSortDataOption constants.