Using Filter in Place in Advanced Filter

It is possible to filter a large dataset in place. In this case, you do not need an output range. You would normally specify criteria range—otherwise you return 100% of the records and there is no need to do the AutoFilter!

In the user interface of Excel, doing a Filter In Place makes sense: You can easily peruse the filtered list looking for something in particular.

Doing a Filter In Place in VBA is a little less convenient. The only good way to program-matically peruse through the filtered records is to use the xlCellTypeVisible option of the SpecialCells method. In the Excel user interface, the equivalent action is to select Edit, Go To from the menu. On the Go To dialog, choose the Special button. In the Go To Special dialog, select Visible Cells Only, as shown in Figure 11.16.

Figure 11.16

The Filter In Place option hides rows that do not match the selected criteria, but the only way to programmatically see the matching records is to do the equivalent of selecting Visible Cells Only from the Go To Special dialog box.

0 0

Post a comment