VBA & Macro Problems

How to Count Visible and Non-Filtered Rows Using Office Scripts in Excel

Jason Miller Oct 16, 2026 870 views

Question details

The user needs to write an Office Script to count only the visible, non-filtered rows within a specific Excel range and store the final count in a variable for use with Power Automate.

How to Count Visible and Non-Filtered Rows Using Office Scripts in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data processing via Power Automate, which requires a cloud-compatible Office Script instead of a traditional local VBA macro.
Observed behavior: The intended outcome is a functional script that accurately calculates the number of rows currently displayed after applying filters, excluding any hidden or filtered rows from the total count.

Before you start

Ensure your Excel workbook is saved in SharePoint or OneDrive, as Office Scripts and Power Automate require cloud storage to function properly. Basic familiarity with TypeScript or JavaScript syntax will also be helpful.

Solution 1Recommended

Create an Office Script Using the getHidden() Method

Loop through the target range and check the visibility status of each row using the getHidden() property, incrementing a counter only for visible rows.

Because Power Automate cannot execute VBA code, you must rely on Office Scripts (based on TypeScript). By using a simple 'for' loop combined with the getHidden() method, you can effectively evaluate each row's visibility.

Open the Code Editor

Navigate to the Automate tab on the Excel ribbon and select 'New Script' to launch the Code Editor panel.

Define the Target Range

Inside your main function, declare a variable for the range you want to evaluate. For example: let range = workbook.getWorksheet("Sheet1").getRange("A1:A10");

Implement the Loop and Counter

Initialize a counter (e.g., let visibleCount = 0;). Use a for loop to iterate through the range's rows. Use an if statement to test visibility: if (!range.getCell(i, 0).getHidden()) { visibleCount++; }

Return the Result to Power Automate

Add a return visibleCount; statement at the end of your script. Make sure your main function's signature indicates it returns a number so Power Automate can capture the variable for subsequent workflow steps.

Create an Office Script Using the getHidden() Method

Zero-Based Indexing: Keep in mind that row and column indices in Office Scripts are zero-based. When using getCell(row, column), the index 'i' should start at 0.

Free Microsoft Office alternative

Looking for a Free, Macro-Enabled Alternative to Microsoft Office?

While Office Scripts and Power Automate are exclusive to Microsoft's cloud ecosystem, WPS Office provides a powerful, desktop-friendly alternative for spreadsheet automation. WPS Spreadsheet fully supports traditional VBA and its own JavaScript Macros (JSA), allowing you to automate tasks like counting filtered rows locally without needing costly cloud subscriptions.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm workbooks seamlessly.
3. Automate with Macros: Access the Developer tab to write or run VBA and JS Macros entirely offline.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm).Built-in support for traditional VBA macros and modern JS Macros.Lightweight desktop installation allowing for fast offline data automation.Free to use for everyday spreadsheet editing, analysis, and formatting.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use a VBA macro to count visible rows in Power Automate?

Power Automate runs in a cloud environment and cannot execute local, desktop-bound VBA macros. It relies exclusively on Office Scripts (which are based on TypeScript/JavaScript) stored on OneDrive or SharePoint to automate Excel online tasks.

What is the Office Script method to check if a row is hidden?

You can use the getHidden() method on a Cell or Range object. Calling range.getCell(rowIndex, columnIndex).getHidden() will return true if the corresponding row or column is hidden, and false if it is visible.

Does getHidden() account for both filtered rows and manually hidden rows?

Yes. The getHidden() method evaluates the actual visibility state of the row on the worksheet. It will return true whether the row was hidden by an active data filter or explicitly hidden manually by a user.

How do I pass the final counted rows variable back to my Power Automate flow?

You need to define your script's main function to return a specific data type, such as function main(workbook: ExcelScript.Workbook): number. By using the 'return' statement at the end of your script, Power Automate will capture the number as an output variable for use in subsequent actions.

Excel & Data Problems