Forum Discussion

imik_plays's avatar
imik_plays
Copper Contributor
Apr 13, 2023

Sorting script for certain sections.

Hey there, I need to automatically sort values inside columns A and B (those values are getting filled in from another sheet), but inside each section only. A section is defined by a cell starting with the # symbol and ending with an empty row (cell).

Then as you can see from the screenshot at the bottom (I believe), each section should be sorted by that criteria.

The best would be if this happened automatically, and the second best would be if it'd adjust everything inside the sheet after I run the macro/script. Any ideas are welcome.

This is what I found that works to select the sections + sort everything inside them from A-Z, but no idea how to apply the custom sorting there.

Also, I use Excel 365.

Excel
Formulas and Functions
office 365
Office Scripts
  • Yutao_Huang's avatar
    Yutao_Huang
    Icon for Microsoft rankMicrosoft
    Apr 15, 2023

    Hi imik_plays ,

     

    Have you considered using Office Scripts? Here is one possible solution written in Office Script:

    function main(workbook: ExcelScript.Workbook) {
  // Get the worksheet
  const sheet = workbook.getWorksheet("Sheet1");

  // Get the information of the used range
  const usedRange = sheet.getUsedRange();
  const startRowIndex = usedRange.getRowIndex();
  const endRowIndex = startRowIndex + usedRange.getRowCount() - 1;
  const startColumnIndex = usedRange.getColumnIndex();
  const columnCount = usedRange.getColumnCount();

  // Find the start row indexes of each section
  const sectionRows = usedRange
    .getColumn(0)
    .getValues()
    .map((row, index) => ({ value: row[0] as string, start: index }))
    .filter(row => row.value.startsWith("#"));

  // Iterate through and sort each section
  for (let index = 0; index < sectionRows.length; index++) {
    const sectionEndRowIndex = index < sectionRows.length - 1 ? sectionRows[index + 1].start - 2 : endRowIndex;
    const sectionRowCount = sectionEndRowIndex - sectionRows[index].start;
    const sectionRange = sheet.getRangeByIndexes(sectionRows[index].start + 1, startColumnIndex, sectionRowCount, columnCount);
    sectionRange.getSort().apply([{ key: 0, ascending: true }], false, false, ExcelScript.SortOrientation.rows);
  }
}

     

    You can create a script by selecting "New Script" from the "Automate" tab and paste the above script code into the editor.

     

    After saving the script, you can then click on "Run" to run it. Please note this script assumes your worksheet is named "Sheet1" so you need to update it for different sheet names.

     

    You can even create a button on the worksheet for this script if you want.

     

    Please let me know if you have any further questions regarding using Office Scripts.

Share

Resources