Microsoft Office Tutorials and References
In Depth Information
ADD FILTER TO SELECTION FUNCTIONALITY
ADD FILTER TO SELECTION
FUNCTIONALITY
Challenge: Access offers a cool feature called Filter to Selection. If you are
looking at a data sheet in Access, click the value XYZ in Field22 and click
Filter to Selection, Access shows you only the records where Field22 is equal
to XYZ . Excel does not offer this feature. Instead, you have to turn on the Filter
(known as AutoFilter in Excel 2003 and before) and choose the desired value
from the Filter dropdown.
Solution: It takes only a few lines of code to replicate this feature in VBA. Add
the following macros to your Personal Macro Workbook. (To get a Personal
Macro Workbook, see “Make a Personal Macro Workbook.”)
Sub FilterToSelection()
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
Selection.AutoFilter _
Field:=ColNum, Criteria1:=ActiveCell
End Sub
Sub AutoFilterToggle()
Selection.AutoFilter
End Sub
Assign the macros to shortcut keys or to custom buttons on your toolbar or
Quick Access toolbar in Excel 2007.
Using the First Macro
To use the fi rst macro, in any data set that has a row of headings at the top,
select one cell in any column. Click the Filter to Selection icon, as shown in
Figure 138.
Figure 138. To see all the General Motors records, select one cell that contains
General Motors and click Filter to Selection.
 
 
Search JabSto ::




Custom Search