Ms Office Tune Up

If you use Excel to store data, chances are you sort that data in some way. Sorting helps you find items and rank numeric values for analysis. However, sorting is a challenge if you reg­ularly add items to the data because it means you have to resort the range after you enter each item. Sorting is also problematic in ranges where the sort column uses a calculation.

For example, Figure 1 shows a products table. The table is sorted on the Gross Margin column (F), the values of which are determined using a formula that requires input from cells in columns D and E. This means that each time a value in column D or E changes, the corresponding Gross Margin value changes, so you need to resort the table.

Figure 1 The products table is sorted on the Gross Margin column (F).

To save time, it is nice if you can keep a range sorted automatically after entering new data or after changing data that affects the sort column. Listing 1 shows a couple of VBA pro­cedures that keep a specified range sorted automatically.

Listing 1 VBA Procedures That Keep the Products Table Sorted Automatically

Sub Auto_Open()
ThisWorkbook.Worksheets(“Products”).OnEntry = “SortProducts”
End Sub

Sub SortProducts()
Dim currCell As Range
Set currCell = Application.Caller
If currCell.Column = 4 Or currCell.Column = 5 Then
Selection.Sort Key1:=Range(“F1”), _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Auto_Open is a macro that runs automatically when the workbook containing the code is opened. In this case, the statement sets the OnEntry event of the Products worksheet to run the SortProducts macro. The OnEntry event fires whenever data entry occurs in the speci­fied object (in this case, the Products worksheet).

The SortProducts procedure checks the value of the Application object’s Caller property, which returns a Range object that indicates which cell invoked the SortProducts macro. In this context, Caller tells us in which cell the data entry occurred, and that cell address is stored in the currCell variable. Next, the macro checks currCell to see if the data entry occurred in either column D or column E. If so, the new value changes the calculated value in the Gross Margin column, so the range needs to be resorted. This is accomplished by running the Sort method, which sorts the range based on the values in column F.

Technorati Tags: sorting range, data entry, excel 2007

Popularity: 2% [?]

If you liked this post, would you please buy me a twelve-ounce lattes for only $2