I am trying to set an Excel script to autofill this column down to the final row of data, and no further. Different tables I am running the script on have different numbers of rows, so the current script with the hardcoded number of rows isn't very useful. I'm looking to get the script to autofill dynamically, regardless of the number of rows in the table it is operating on.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
selectedSheet.getRange("B2").autoFill("B2:B237", ExcelScript.AutoFillType.fillDefault);
}
I tried just hardcoding the .autofill() to B2:B2000, but that filled blank rows after the last row, which I do not want. Then I tried B:B, which broke the script. Any feedback would be greatly appreciated. Thanks!
Please try
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let usedLastRow = selectedSheet.getUsedRange().getLastCell().getRowIndex();
selectedSheet.getRange("B2").autoFill(`B2:B${usedLastRow+1}` , ExcelScript.AutoFillType.fillDefault);
}