You’re offline. This is a read only version of the page.
Announcements
15
I used the "record actions" feature in excel to create an Office Script that separates data into multiple columns using the Text to Columns Wizard. When recording actions for this script, I only used 3 rows of data and intended for the script to affect the entire first column (A) for when I eventually run it on hundreds of rows in a Power Automate flow. My script is attached below and I'm not sure what I need to change to get the range of non-empty cells in the A column.
Share
Reportfunction main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Text to columns on range A1:A3 on selectedSheet
for (let row = 0; row < selectedSheet.getRange("A1:A3").getRowCount() ; row++) {
let sourceRange = selectedSheet.getRange("A1:A3");
let destinationRange = selectedSheet.getRange("B1");
let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[,]/)
destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
}
}All of my data will be entered one after the other, so the first empty row will mark the end of the column.
This also might not be the correct forum to ask this question, so if there is a forum I should post to instead, please tell me in the replies and I will move my post there!
Hi @Wes_G ，
Please try:
All responses (
Answers (
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let selectedRange = selectedSheet.getRange("A1").getExtendedRange(ExcelScript.KeyboardDirection.down);
let rowCount = selectedRange.getRowCount();
for (let row = 0; row < rowCount; row++) {
let sourceRange = selectedRange;
let destinationRange = selectedSheet.getRange("B1");
let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[,]/)
destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
}
}
Best Regards,
Bof
