VBA & Macro Problems

How to Use Office Scripts to Copy Filtered Rows in Excel

Phi Hung Vo Oct 9, 2026 871 views

Question details

The user needs to copy only visible, filtered rows using Office Scripts for an Excel file stored in SharePoint, as traditional VBA methods like SpecialCells are not supported.

How to Use Office Scripts to Copy Filtered Rows in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the extraction of filtered data in Excel for the web or SharePoint using Office Scripts.
Observed behavior: Office Scripts may incorrectly copy hidden rows if getVisibleView is misused, and VBA methods such as SpecialCells(xlCellTypeVisible) return errors because they are incompatible with Office Scripts.

Before you start

Ensure your Excel file is saved in OneDrive or SharePoint and opened in Excel for the Web, as Office Scripts rely on cloud infrastructure and TypeScript/JavaScript logic rather than traditional VBA.

Solution 1Recommended

Filter Data Manually within the Office Script Array

The most reliable method in Office Scripts is to read the entire range into an array, filter the data using JavaScript logic, and write the result to the destination worksheet.

Because Office Scripts do not support VBA's SpecialCells method, you need to handle data manipulation in memory. This involves extracting the data, using standard JavaScript array filtering to isolate the rows that meet your criteria, and pasting the cleaned array to a new location.

Get the used range

In your Office Script, use workbook.getWorksheet('SheetName').getUsedRange() to define the range containing your source data.

Read the values into an array

Call the getValues() method on your range object to store the data in a 2D TypeScript/JavaScript array variable.

Filter the array logically

Use the native JavaScript .filter() method on your array to return only the rows that match your specific filtering conditions (e.g., column index equals a specific value).

Write to the destination worksheet

Select the target worksheet, define a target range of the exact same dimensions as your filtered array, and use setValues() to paste the filtered data.

Filter Data Manually within the Office Script Array

Best Practice: Filtering data in-memory (using arrays) is significantly faster in Office Scripts than interacting with the worksheet cell by cell.

Solution 2

Use getVisibleView() on an AutoFiltered Range

If you have already applied an AutoFilter to your worksheet, you can extract just the visible cells using the getVisibleView() method.

Advanced Spreadsheet Automation

Automate Data Filtering Locally with WPS Office JS Macros

If you want to use modern JavaScript for spreadsheet automation but prefer a powerful desktop application over cloud-restricted Office Scripts, WPS Office provides built-in JS Macros (JSA). You can easily filter and copy data locally without relying on SharePoint.

1. Open WPS Spreadsheets: Launch WPS Office and open your workbook in the Spreadsheets module.
2. Access the JS Macro Editor: Navigate to the 'Tools' tab on the ribbon and click on 'JS Macro' to open the integrated JavaScript editor.
3. Write your array filtering script: Write standard JavaScript code to retrieve the active sheet's UsedRange, read the .Value2 property into an array, and use array.filter() to extract your desired rows.
4. Output to destination sheet: Assign the filtered array back to a new range's .Value2 property on your destination sheet, then run the macro.

Write macros in standard JavaScript (JSA) directly within the WPS desktop appHigh compatibility with Microsoft Excel data formats (.xlsx, .xlsm, .csv)Run automated scripts locally without requiring SharePoint or cloud connectivityFully free and lightweight alternative to heavy Microsoft Office subscriptions

microsoft office alternative - wps office

Frequently Asked Questions

Why does SpecialCells(xlCellTypeVisible) throw an error in Office Scripts?

SpecialCells is a specific method within the VBA object model designed for traditional desktop Excel. Office Scripts operate entirely on TypeScript/JavaScript and run in Excel for the web, which does not support VBA commands. You must use Office Scripts API methods like getVisibleView().

Can I run Office Scripts on a local Excel file saved on my desktop?

No. Office Scripts are designed exclusively for Excel workbooks stored in Microsoft's cloud services, such as SharePoint or OneDrive. To run them, the file must be hosted online.

How do I troubleshoot if my Office Script is still copying hidden rows?

Use the console.log() function in the Office Scripts editor to print your array data before it gets pasted to the destination sheet. This helps verify if your filtering logic is correctly identifying and removing the hidden rows in memory.

Excel & Data Problems