As you have seen, the AutoFilter and Advanced Filter features can be combined with VBA code to provide flexible ways for users to extract information from data lists. By combining these features with ActiveX controls, such as combo boxes and command buttons, you can make them readily accessible to all levels of users. You can use the macro recorder to get an indication of the required methods and adapt the recorded code to accept input from the ActiveX controls.

Tables provide a way to formalize data structures and tools to maintain data lists. Sorting and filtering is facilitated when the data is in a table. The Tables ListObject object makes it easier to generate VBA references to your data and to manipulate it programmatically.

However, you need to take care if you work with non-U.S. date formats. You need to bear in mind that VBA requires you to use U.S. date formats when you compare ranges of dates using AutoFilter. If this interests you, you should check out Chapter 21, which deals with international programming issues.

Also, when you want to detect which rows have been hidden by AutoFilter, you need to be aware that the Hidden property of the Range object can only be applied to entire worksheet rows.

Advanced Filter provides the VBA programmer with very powerful filtering in Excel. You can set up much more complex criteria with Advanced Filter than you can with AutoFilter, and you can copy filtered data to a specified range. You can also use Advanced Filter to copy filtered data from one workbook to another.

The Data Form feature makes it very easy to set up a data maintenance macro. However, you should apply the name Database to your data list if the top-left corner of the list is not in the A1 cell.

0 0

Post a comment