Allowing Users To Sort By Columns

To allow users to sort by columns

To sort the sheet based on a specific column using code

Overview

By default, when a user clicks a column header, the column is selected. You can allow users to click on column headers to sort the data by that column. Sorting toggles between ascending and descending with each click. You can specify whether a sort indicator appears in the column header to show what type of sort has occurred.

The user can also click on a column to apply a primary sort and then control-click on a second column to apply a secondary sort.

For example, assume the sheet contains the following data:

If you set the UserColAction property to allow user sorting, when the user clicks the first column header, the data is displayed in ascending order and a sort indicator appears in the column header:

If the user clicks the column header a second time, the data is displayed in descending order and the sort indicator changes to indicate descending order:

You can also prevent any specific column from being sorted when the user clicks the column header.

Notes:

User sorting causes all data to be sorted. It does not occur on a column-by-column basis. In other words, when you click on a column to sort, the data in all the rows moves according to the data in the column on which you are sorting.
User sorting will not occur on columns that contain spanned cells.

You can also sort the sheet by a specific column using code without any user action. If you set the ColUserSortIndicator property in code to either 1 (Ascending) or 2 (Descending) before you set the UserColAction property to 1 (Sort) or 2 (SortNoIndicator), the Spread control will search from left to right until if finds the first column for which the ColUserSortIndicator property is set and sort the sheet automatically based on that indicator. For example, if you set the ColUserSortIndicator property for the second column to 1 (Ascending) and you set the UserColAction property to 1 (Sort), the data will be sorted by the second column automatically in ascending order.

Tip: If you want to sort the sheet based a specific column using code, you should never set the ColUserSortIndicator property for more than one column and you must set the ColUserSortIndicator property before you set the UserColAction property.

The BeforeUserSort event occurs when the user clicks the column header but before the data is sorted. You can use the BeforeUserSort event to cancel the sort or perform a manual sort using code in the event. The AfterUserSort event occurs after the user clicks a column header and after the data is sorted.