Advanced Filter

A powerful way to filter data from a list is to use Advanced Filter. You can filter the list in place, like AutoFilter, or you can extract it to a different location. The extract location can be in the same worksheet, in another worksheet in the same workbook, or in another open workbook. In the following example, the data for NSW and VIC has been extracted for the first quarter of 2007. The data has been copied from the workbook containing the data list to a new workbook.

The source data can be in a Table or can be in a normal range. In the following examples, the data is in a normal range named Database.

When you use Advanced Filter, you specify your criteria in a worksheet range. An example of a Criteria range is shown in A1:C3 of the screen in Figure 6-10. This worksheet is in a workbook called DataList6 .xlsm. The data list is in DataListS.xlsm, which contains the same data used in the AutoFilter examples.

Weekly Budget Template
Figure 6-10

The top row of the Criteria range contains the field names from the list that you want to filter on. You can have as many rows under the field names as you need. Criteria on different rows are combined using the OR operator. Criteria across a row are combined using the AND operator. You can also use computed criteria in the form of logical statements that evaluate to True or False. In the case of computed criteria, the top row of the Criteria range must be empty or contain a label that is not a field name in the list, such as Calc in this case.

When you create computed criteria, you can refer to the data list field names in your formulas, as you can see in the Formula bar above the worksheet. The Formula bar shows the contents of C2, which is as follows:

The formula in C3 is identical to the formula in C2.

The criteria shown can be thought of as applying this filter:

(State=NSW AND Date>=Jan 1, 2007 AND Date<=Mar 31, 2007) OR _

(State=VIC AND Date>=Jan 1, 2007 AND Date<=Mar 31, 2007)

Because the field names are not workbook names, the formulas evaluate to a #NAME? error.

To facilitate the Advanced Filter, the data list in the DataListS.xlsm workbook has been named Database. In the DataList6.xlsm workbook, A1:C3 has been named Criteria and A6:G6 has been named Extract. If you carry out the Advanced Filter manually, selecting the Data tab of the Ribbon and clicking the Advanced button in the Sort & Filter group, you see the dialog box in Figure 6-11, where you can enter the names as shown. The List Range entry, which is obscured, is DataList5.xlsm!Database.

Advanced Filter _

Copy to another location ¬°.¬°strange! DataUstS.rfsmlDatat Criteria rsnge: ffiiaa-j _. i

Figure 6-11

To automate this process, the command button with the Extract Data caption runs the following Click event procedure:

Private Sub CommandButton1_Click() Dim rngData As Range Dim rngCriteria As Range Dim rngExtract As Range

'Define Database, Criteria & Extract Ranges

Set rngData = Workbooks("DataList5.xlsm").Worksheets("Sales").Range("Database") Set rngCriteria = ThisWorkbook.Worksheets("Sales").Range("Criteria") Set rngExtract = ThisWorkbook.Worksheets("Sales").Range("Extract")

'Extract data with Advanced Filter rngData.AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=rngCriteria, _ CopyToRange:=rngExtract, _ Unique:=False

End Sub

The event procedure defines three object variables referring to the Database, Criteria, and Extract ranges. It then runs the AdvancedFilter method of the Database Range object.

0 0

Post a comment