VBA & Macro Problems

How to Automatically Sort Excel Rows by Custom Job Rank and Amount

Huma Ashraf Ch Oct 21, 2026 868 views

Question details

The user needs to automatically sort a dynamic Excel table based on a custom job hierarchy (e.g., Boss, Manager) and historical amounts.

How to Automatically Sort Excel Rows by Custom Job Rank and Amount

Product: Excel
Device & OS: not provided
Scenario: Managing a frequently updated table containing employee job roles and transaction amounts that requires consistent, multi-level sorting.
Observed behavior: The user requires an automated method, such as a VBA macro, to apply a multi-level custom sort so the table doesn't have to be sorted manually every time new data is added.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in the ribbon to access the VBA editor.

Solution 1Recommended

Use an Excel VBA Macro with CustomOrder

Automate the sorting process by writing a VBA macro that uses a custom list for job roles and a standard sort for the amount.

By default, Excel sorts alphabetically or numerically. To sort by a specific hierarchy like organizational roles, you must define a custom order. Using a VBA macro streamlines this process so you can apply multi-level sorting to frequently updated datasets with a single click.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click 'Insert', then select 'Module' to create a new script window for your sorting macro.

Clear Existing Sort Fields

Begin your macro by clearing any previous sort states. Use the code: ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear to prevent application errors.

Add Sort Fields with CustomOrder

Add your first sort field for the amount column. Then, add the second sort field for the job rank column and include the CustomOrder property, for example: CustomOrder:="Boss,Manager,Employee,Stock Clerk".

Apply the Sort

Set your table range and apply the sort using the .Apply method. Save the macro and run it whenever your table data is updated.

Macro Error Prevention: If your macro produces an error, ensure you have included the code to clear existing sort fields before adding new ones, and verify that your target range matches your actual table coordinates.

Solution 2

Manually Apply a Custom Sort List

Create a custom list through the Excel Data menu if you prefer not to use VBA macros for multi-level sorting.

Automate Data Sorting with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA and macros, allowing you to easily automate multi-level sorting tasks with custom organizational lists. It provides a seamless environment for data management.

1. Open Your Spreadsheet: Launch WPS Office and open your .xlsm file containing the data.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon.
3. Open the VBA Editor: Click on the 'Macros' or 'Visual Basic' icon to access the scripting interface.
4. Run Your Macro: Insert your CustomOrder VBA code and execute it to automatically sort your table.

Fully supports VBA and macro execution for automated sortingHighly compatible with Microsoft Excel (.xlsx and .xlsm) formatsLightweight application with fast processing for large datasetsFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA sort macro produce an error on the CustomOrder line?

This commonly happens if the previously applied sort fields are not cleared. Always start your sorting block with `Sort.SortFields.Clear`. Also, verify that the spelling of the items in your `CustomOrder` string perfectly matches the data in your worksheet.

Can I use the custom sort feature without knowing VBA?

Yes. You can manually create a Custom List by going to File > Options > Advanced > Edit Custom Lists, or by using the Data > Sort menu and choosing 'Custom List' under the Order dropdown.

How do I ensure my custom sorting macro is saved for future use?

You must save your workbook as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, all VBA code, including your sorting macro, will be permanently stripped out and lost.

Excel & Data Problems