Use a Dynamic Used Range for Office Scripts Autofill in Excel
Question details
The user needs to autofill formulas in Excel with Office Scripts by calculating the populated row count at runtime instead of using a hard-coded range such as D11203.
- Product
- Microsoft Excel with Office Scripts
- Device & OS
- not provided
- Scenario
- Automating formula fills in a worksheet where the number of populated rows changes at runtime.
- Observed behavior
- The desired behavior is for the script to determine the used row count and fill formulas only through the actual data area, avoiding a fixed autofill end row.
Open the workbook in an Office Scripts-enabled Microsoft 365 Excel environment, and confirm the worksheet contains a populated column that can be used to calculate the dynamic row count.
Build the Autofill Range from getUsedRange and getResizedRange
Use this method when you want Office Scripts to autofill formulas through the current data area without hard-coding the final row.
Hard-coding a final row such as D11203 breaks when the data grows or shrinks. getUsedRange reads the existing populated area, and getResizedRange expands D2 by the remaining row count so the fill follows the actual data.
In the Excel for the web Automate tab, open a new script and start with `function main(workbook: ExcelScript.Workbook) {`. Assign `const sheet = workbook.getActiveWorksheet();`, then calculate `const dataSize = sheet.getRange("D:D").getUsedRange().getRowCount() - 1;`.
Call `sheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right);`, then set the header and formula with `sheet.getRange("D1:D2").setFormulasLocal([["Duration_Minutes"], ["=(E2/60)"]]);`. Replace the formula with your own column references if needed.
Use `sheet.getRange("D:E").setNumberFormatLocal("General");` and then `sheet.getRange("D2").setNumberFormatLocal("0.00");` so the inserted column displays values in the intended format.
Run `sheet.getRange("D2").autoFill(sheet.getRange("D2").getResizedRange(dataSize - 1, 0), ExcelScript.AutoFillType.fillDefault);` to fill from D2 down to the row indicated by the used range count.
Use WPS Office as a Free Spreadsheet Alternative for Macro and Autofill Workflows
WPS Office is a free, lightweight Office suite that opens Microsoft Excel workbooks, preserves .xlsx and .xlsm compatibility, and offers a familiar ribbon interface for everyday spreadsheet and macro tasks. If you do not need Office Scripts specifically, WPS Spreadsheet provides a practical alternative for viewing, editing, and automating routine Excel files without a Microsoft 365 subscription.
- 1. Download and install WPS Office: Go to the official WPS Office download page, choose your platform, download the free installer, and complete the installation.
- 2. Open your Excel workbook in WPS Spreadsheet: Launch WPS Office, click Menu > Open, and select your .xlsx or .xlsm workbook. The file opens in WPS Spreadsheet with the familiar ribbon layout.
- 3. Use formulas, autofill, or macros for routine tasks: Use the fill handle or formula tools to repeat calculations, or use the macro options available in your WPS edition for supported VBA workbooks. Check the WPS documentation for macro availability on your version.
Frequently Asked Questions
Can Office Scripts autofill without hard-coding the final row?
Yes. In Excel for the web, use getUsedRange() to calculate the populated row count and getResizedRange() to expand the formula cell through the remaining data rows before calling autoFill().
Does this Office Scripts solution work in WPS Office?
No. Office Scripts is a Microsoft Excel for the web feature and is not supported in WPS Office. In WPS Office, use the available formula autofill tools or supported macro options for similar spreadsheet tasks.
What should I change in the example script for my worksheet?
Adjust the column letters, formula, and number format. The example inserts a new column D and fills =(E2/60); replace D, E, the formula expression, and the number format with the values that match your data.
How does getResizedRange(dataSize - 1, 0) determine the autofill target?
dataSize is the populated row count minus the header row. Passing dataSize - 1 as the row resize count expands D2 down through the remaining rows, so autoFill fills the correct dynamic range.
Where do I run this Office Scripts code?
Open Excel for the web with a Microsoft 365 work or school account, go to the Automate tab, click New Script, paste the code into the editor, and click Run.
