Add Sorting and Filtering Capabilities to Reports - Part I

One feature that always helps the usability of reports is the addition of various sorting (i.e. re-arranging the order of records) and filtering (i.e. show/hide records based on certain criteria) capabilities for the end-user. The easiest way to include such functionality is to add "Auto-Filter" to the header row using the following code:

.Range("A3:L3").AutoFilter

The Range() object above would contain all the headers of the list. Run the code again, you would remove the Auto-Filter.

Problem solved, right? Except things rarely get that easy. In reality people who are not Excel geeks like you and me may not feel comfortable using Auto-Filter and many of its functions. In oder to create a more user-friendly report, I always recommend using drop-down lists to let the user choose which metric to sort or filter. See a snapshot below:

You can see that once again we are using our physician record file "Mikoudai Inc Sales Hierarchy.xls" as the example. The above drop-down list (also called Combo Box) resides in the worksheet "Nat Data", and obtains its listed items from the range "A1:A12" in another worksheet named "DropDownList". The listed items correspond with all the headers we have for the physician record list, such as "MD #", "Region #", "Address", etc. The "Cell Link" property refers to range "B1" in "DropDownList". Further assume that we would like to have an ascending order for all the columns except "Level" (we want to show "RxDx" first) when sorting. Here is the macro:

Don't forget to associate the macro with the drop-down list. Download the Excel file here.

Application.Calculation = xlCalculationManual stops the automatic re-calculation of all the cell formulas in the entire workbook, until it is reset back to Automatic Calculation. This helps improve the speed of the macro execution.

On Error Resume Next prevents the macro execution from being stopped by any (potential) errors generated. The macro will just ignore the error and skip the line that causes the error and goes to the next line. Of course any error may render the entire macro unusable, however at least your user will not be alerted by the sudden appearance of an error message.

.Range().Sort is the main method that does the sorting job. Key1 denotes the first "field" - under most circumstances being the column - to be sorted. If necessary, Key2 (and Key3) may be added to perform up to three sorts. "Order1" decides if the sorting for Key1 is A->Z (Ascending) or Z->A (Descending). "Header" tells the macro whether the header row has been included in the Range() object and this is needed if we don't want the header row to be sorted as well (and we don't).

In the next post I will show you how to do a sorting based on customized order.