Forum Discussion
Select entire sheet then sort on one column (automated)
Total newb here... This should be very simple. Automate > Record Actions Right-Click to the left of Column A (This highlights the entire sheet) Sort > Custom Sort Sort on Col J ("Amount" is the ...
I'm not sure how recorder works, the script could be
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getUsedRange() const sortedColumnIndex = range .getRow(0) // get first row, i.e. headers .find("Amount", { completeMatch: true }) .getColumnIndex() range.getSort().apply( // apply to entire range [{ ascending: true, // sort order key: sortedColumnIndex // on column to sort }], false, //case insensitive true, // range with headers ExcelScript .SortOrientation .rows // sort by rows ) }We select only range with data, not entire sheet. Find column number for the cell with "Amount". Apply sorting to entire range, not to one column. Use above column as one on which we do sorting.
I'm not sure how recorder works, the script could be
function main(workbook: ExcelScript.Workbook) {
const range = workbook
.getActiveWorksheet()
.getUsedRange()
const sortedColumnIndex = range
.getRow(0) // get first row, i.e. headers
.find("Amount", { completeMatch: true })
.getColumnIndex()
range.getSort().apply( // apply to entire range
[{
ascending: true, // sort order
key: sortedColumnIndex // on column to sort
}],
false, //case insensitive
true, // range with headers
ExcelScript
.SortOrientation
.rows // sort by rows
)
}
We select only range with data, not entire sheet. Find column number for the cell with "Amount". Apply sorting to entire range, not to one column. Use above column as one on which we do sorting.
- Thank you. This worked perfectly on a clean test file, but not on my .CSV file from another app. I would say I have some non-printable characters in my data that are messing things up.
Non-printable characters also could be removed by the script, but for that desirably to know does your text includes only English alphabet or not; if not which one. If possible non-printable characters are known - even better.
