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 regularly 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 6.9 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.

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 6.2 shows a couple of VBA procedures that keep a specified range 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 specified 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.

• Anti Aging Makeup Tricks and Tips pt 1 There are all kinds of makeup tricks and tips out there, but choosing the right ones for your own personal...
• Photoshop Tips & Tricks which will be found only here Publish at Scribd or explore others: How-To & DIY eBooks Technology-General Photoshop ......
• 13 Quick Tips for Better Tennis There are a lot of tennis tips and tricks floating around out there, but many are complicated and complex, and...
• iPad's Hidden Keyboard Functions: Tips & Tricks In this video I demonstrate the hidden keys and functions on the iPad keyboard.......
• What's The Purpose Of A Company's Blog It's a truth that the majority companies have a blog. Who had that dusty previous institutional website has now optimized...