Find the Used Range in Excel Office Scripts
Question details
Find the worksheet's used range with an Office Script.

- 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.
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.
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.
In Excel, open the workbook and select Automate > New Script.
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()}`); }
Select Run in the script editor, then check the script output for the used-range address or the empty-sheet message.

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.

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.




