Auto Filter

The AutoFilter feature is a very easy way to select data from a list. As you might expect, AutoFilter works with tables or with any list of data. You can activate AutoFilter by selecting a cell in your data, selecting the Data tab on the Ribbon, and clicking the Filter button in the Sort & Filter group. Dropdown menu buttons will appear beside each field name, as shown in Figure 6-5. If you want an exact match on a field such as Customer, all you need to do is click the drop-down beside the field and check the required match, as shown in Figure 6-5.

Figure 6-5

If you record this process with a normal range of data, you will get code like the following:

ActiveSheet.Range("$A$5:$G$433").AutoFilter Field:=2, Criteria1:="Pradesh"

If you record the same process in a table, you will get code like the following:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _ "Pradesh"

If you select two items, you will get code like the following:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _ "=Kee", Operator:=xlOr, Criteria2:="=Pradesh"

If you select more than two items, you will get code like the following:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _ Array("Kee", "Pradesh", "Roberts"), Operator:=xlFilterValues

The following code clears the filter and displays all the data for the field:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2

0 0

Post a comment