VBA & Macro Problems

Find the Used Range in Excel Office Scripts

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Find the worksheet's used range with an Office Script.

Find the Used Range in Excel Office Scripts
Product
Microsoft Excel Office Scripts
Device & OS
not provided
Scenario
The user wants to identify the area containing worksheet data, similar to finding the endpoint used by Ctrl+Shift+End.
Observed behavior
The script should return the used range, including its bottommost used row and rightmost used column, and handle an empty worksheet.
Before you start

Open the workbook in a version of Excel where Office Scripts is available and confirm that the Automate tab is visible. Decide whether formatting alone should count as used when identifying the range.

Solution 1Recommended

Get the worksheet's used range with Office Scripts

Use getUsedRange() to identify the worksheet's used area and check whether a range was returned.

The used range spans the cells Excel considers used, including cells with formatting. It is not necessarily the same selection as Ctrl+Shift+End, which starts from the active cell.

1
Open the script editor

In Excel, open the workbook and select Automate > New Script.

2
Add the used-range code

Replace the editor content with: function main(workbook: ExcelScript.Workbook) { const worksheet = workbook.getActiveWorksheet(); const usedRange = worksheet.getUsedRange(); if (!usedRange) { console.log("The worksheet is empty."); return; } console.log(`Used range: ${usedRange.getAddress()}`); }

3
Run the script

Select Run in the script editor, then check the script output for the used-range address or the empty-sheet message.

Get the worksheet's used range with Office Scripts
Count values only: Use worksheet.getUsedRange(true) if you want to consider cells with values rather than formatting alone. An entirely blank worksheet can return undefined, so keep the check before using the range.
Free Microsoft Office alternative

Work with Spreadsheet Data in WPS Office

WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Microsoft Excel format compatibility helps you move existing workbooks into WPS; Excel Office Scripts remain a Microsoft Office feature.

Navigate and edit worksheet data in WPS SpreadsheetsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does getUsedRange() select cells like Ctrl+Shift+End?

No. worksheet.getUsedRange() returns a range object for the worksheet's used area; it does not reproduce a selection starting from the active cell.

How can I ignore cells that only have formatting?

Call worksheet.getUsedRange(true) to determine the used range based on cells with values rather than formatting alone.

What happens if the worksheet is empty?

getUsedRange() can return undefined for an entirely blank worksheet. Check the result, for example with if (!usedRange), before calling methods on it.

How do I see the address of the used range?

After checking that the range exists, call usedRange.getAddress() and use console.log() to display the address in the script output.