| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The easiest way to filter data in Excel is by using the AutoFilter command on the Data menu (click Filter, then click AutoFilter). The AutoFilter command creates drop-down boxes for each column of a range of data. Each drop-down box contains all of the values that appear in that column, and you can filter on any of them.
To toggle the AutoFilter feature from VBA, apply the AutoFilter method to the range of data that you want to filter. If the AutoFilter feature is already on, applying the AutoFilter method will turn it off, and vice versa, so it's a good idea to check the value of the AutoFilterMode property before applying the method to determine whether the AutoFilter feature is on or off.
You can also use the AutoFilter method with an alternative syntax to filter the range on specific criteria. The syntax takes four arguments: Field, Criteria1, Operator, and Criteria2. Use the Field argument to specify the number of the column to filter, and the Criteria1 argument to specify a single criterion. Use the Operator and Criteria2 arguments to specify an additional criterion.
Note The number that you specify for the Field argument must take into account any hidden fields that precede it in the worksheet. For example, if column 2 is hidden, you must still specify 3 to filter on column 3.
The following code fragment filters and sorts data on a specified column.
' Return reference to worksheet.
Set wksWorksheet = ThisWorkbook.Worksheets(strWksName)
' Store result range for query table.
Set rngRange = wksWorksheet.QueryTables(varIndex).ResultRange
' Check whether AutoFilter is off.
If wksWorksheet.AutoFilterMode = False Then
' Turn AutoFilter on.
rngRange.AutoFilter
End If
' Show all data in worksheet, in case a filter was
' in effect when procedure began running.
' Suppress the error that occurs if all data is already showing.
On Error Resume Next
wksWorksheet.ShowAllData
On Error GoTo FilterAndSortQueryTable_Err
' Filter on specified criteria.
rngRange.AutoFilter lngField, strCriteria1, lngOperator, strCriteria2
' Sort on same column that was filtered.
rngRange.Sort rngRange.Columns(lngField), lngOrder
This code fragment is taken from the FilterAndSortQueryTable procedure in the modFilterSort module in the Northwind.xls sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.
You can also use the AdvancedFilter method to filter data in Excel. For more information, search the Microsoft Excel Visual Basic Reference Help index for "AdvancedFilter method."