VBA & Macro Problems

How to Create a VBA Multi-Criteria Sort in Excel 2019

Tauseeq Magsi Oct 15, 2026 868 views

Question details

The user needs a custom, button-driven VBA script to sort a specific cell range based on complex, multi-tiered conditional logic across multiple columns.

How to Create a Multi-Criteria Sort Using VBA in Excel 2019

Product: Microsoft Excel 2019
Device & OS: not provided
Scenario: Sorting the range B7:AS55 using criteria in columns AL and AS, prioritizing rows where AS is nonblank, followed by AL=1, and lastly AL=0 with blank AS cells.
Observed behavior: Standard Excel sorting options cannot natively process this nested conditional blank/nonblank priority logic, requiring a custom VBA solution.

Before you start

Ensure the Developer tab is enabled in your Excel ribbon and always save a backup copy of your workbook before running new VBA macros to prevent accidental data loss.

Solution 1Recommended

Write and Assign a Multi-Criteria VBA Sort Macro

Use Excel's SortFields object in VBA to handle multiple columns and assign the macro to a clickable worksheet button.

When dealing with complex sorting rules, VBA's native Sort object is highly effective. You can define multiple SortFields to process columns sequentially based on your specific priorities.

For highly conditional logic, such as prioritizing nonblanks then 1s then 0s, it is often best to create a hidden 'helper' column within the VBA script to assign numeric sort weights, then sort the range based on that helper column.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a blank workspace for your code.

Define the Helper Sorting Macro

Write a sub-procedure that iterates through rows 7 to 55. Use If/ElseIf statements to check the conditions of columns AS and AL, writing a priority number (1, 2, or 3) into a hidden helper column (e.g., column AT).

Apply the SortFields Logic

In the same macro, clear existing sort rules with ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear. Then, add a new SortField targeting your helper column in ascending order, applying it to Range("B7:AT55").

Clear Helper Data and Finish

Include a line of code at the end of the macro to clear the contents of the helper column so the worksheet remains clean after the sort completes.

Assign Macro to a Button

Return to the worksheet, go to Developer > Insert > Button (Form Control), draw the button on your sheet, and assign your new sorting macro to it.

Write and Assign a Multi-Criteria VBA Sort Macro

Helper Columns Simplify Complex Logic: Excel's default SortFields cannot easily evaluate 'Is Not Blank' as a direct primary key. Translating conditions into numeric weights (1, 2, 3) in a temporary helper column guarantees perfect accuracy.

Solution 2

Prepare an Anonymized Sample for Complex Debugging

If the custom VBA logic fails, recreate the environment in a safe file to share with developer communities for precise troubleshooting.

Create and Run Multi-Criteria Macros with WPS Office

WPS Office fully supports VBA macros, allowing you to seamlessly run, edit, and assign complex multi-criteria sorting scripts just like in Microsoft Excel. It offers a familiar interface for managing developer tools effortlessly.

1. Install WPS Office: Download and install WPS Office, then open your macro-enabled spreadsheet.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Open the VBA Editor: Click 'Macros' or press ALT + F11 to open the built-in VBA editor.
4. Run Your Code: Paste your multi-criteria sorting code, save the module, and assign it to a shape or button directly on the sheet.

Full compatibility with Microsoft Excel VBA macros (.xlsm and .xlsb formats).Built-in Developer tools for editing modules and assigning macros to form buttons.Lightweight, fast execution for large data ranges and complex sorting tasks.Free alternative offering professional spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I sort by a blank or non-blank cell condition in standard Excel without VBA?

Yes, you can use Excel's Custom Sort or Filter options to separate blank and non-blank cells, but automating this alongside secondary conditional logic is best handled via a VBA macro assigned to a button.

Why does my VBA Sort macro fail on ranges with merged cells?

Excel cannot sort ranges that contain merged cells of different sizes. Ensure that the range B7:AS55 has all merged cells separated before executing the SortFields.Apply command in VBA.

What is the difference between SortFields.Add and SortFields.Add2 in VBA?

SortFields.Add2 was introduced in newer versions of Excel (like 2016 and 2019) to support new data types. If your code needs to be backward compatible with older Excel versions, use SortFields.Add.

Excel & Data Problems