VBA & Macro Problems

How to Autofill Excel Formulas Dynamically with Office Scripts

Khadija Khan Oct 23, 2026 869 views

Question details

The user needs to copy formulas across multiple columns down to the bottom of a continuously expanding table using Office Scripts, avoiding static ranges.

How to Autofill Excel Table Formulas Automatically with Office Scripts

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating a spreadsheet workflow where new rows of data are frequently added, requiring formulas to expand dynamically based on the current data length.
Observed behavior: The user wants the destination range to expand intelligently to match the exact row count of the current region instead of being locked to a fixed range like M2:V1000.

Before you start

Ensure you have an active Microsoft 365 enterprise or educational subscription that supports Office Scripts, and verify that your workbook is saved to OneDrive or SharePoint.

Solution 1Recommended

Use getCurrentRegion() to Autofill Formulas Dynamically

Utilize the getCurrentRegion() property in Office Scripts to automatically detect the full block of data and autofill the exact number of active rows.

Hardcoding a range like 'M2:V1000' can slow down your workbook or overwrite empty cells outside your actual dataset. By capturing the row count of the current contiguous region, the script adapts dynamically whenever rows are added or removed.

Open the Code Editor

Navigate to the 'Automate' tab on the Excel ribbon and click on 'New Script' to open the Office Scripts Code Editor pane.

Insert the Dynamic Autofill Script

Replace the default code with the following script to calculate the row count: function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getWorksheet('Input'); let dataRange = sheet.getRange('M2:V2').getCurrentRegion(); sheet.getRange('M2:V2').autoFill('M2:V' + dataRange.getRowCount(), ExcelScript.AutoFillType.fillCopy); }

Save and Run

Click 'Save script' at the top of the editor. Click 'Run' to instantly fill the formulas from M2:V2 down to the final row of your current table region.

Use getCurrentRegion() to Autofill Formulas Dynamically

Match Worksheet Names: Ensure that your target worksheet is actually named 'Input'. If your sheet has a different name, update the string inside workbook.getWorksheet() accordingly.

Advanced Automation in WPS Office

Automate Data and Formulas Easily with WPS Spreadsheet

WPS Office provides seamless workflow automation through robust JavaScript Macro (JSA) support and intuitive built-in Table features. You can effortlessly manage growing datasets and auto-expand formulas without dealing with complex TypeScript syntax.

1. Convert to a Table: Highlight your dataset and press Ctrl+L or Ctrl+T. When you format data as a Table in WPS Spreadsheet, formulas in any column are automatically copied down as soon as you type in a new row.
2. Use the Smart Fill Handle: Select the cell containing your formula, hover over the bottom-right corner until the cursor becomes a cross, and double-click. WPS will intelligently autofill the formula to the exact bottom of your adjacent data.
3. Leverage WPS JS Macros: Go to the 'Tools' tab and select 'JS Macro'. You can record actions or write standard JavaScript to automate tasks like range auto-filling, saving you hours of manual formatting.

Fully compatible with Microsoft Excel (.xlsx, .xlsm) files and formulas.Powerful JavaScript Macro editor for creating custom automated workflows.Built-in 'Format as Table' feature that natively extends formulas to new rows.Lightweight, fast, and available completely free.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Office Script autoFill method return an error?

Errors typically occur if the destination range does not properly intersect with the source range, or if the initial range (e.g., M2:V2) does not contain valid data or formulas to copy. Always ensure the calculated range string creates a valid reference.

How exactly does getCurrentRegion() work in Excel scripts?

The getCurrentRegion() method identifies the contiguous block of data surrounding the specified cell or range. It stops expanding when it encounters completely blank rows or columns, effectively locating the boundaries of your active dataset.

Can I automatically expand formulas without writing any scripts?

Yes. The simplest way to handle this in both Excel and WPS Spreadsheet is to format your data range as a Table (Insert > Table). Tables automatically copy existing column formulas to any newly added rows without requiring macros or scripts.

Excel & Data Problems