VBA & Macro Problems

How to Count Empty Cells in Column S Using Office Scripts

Tauseeq Magsi Oct 9, 2026 868 views

Question details

The user needs to find a programmatic way using an Office Script to count all blank or empty cells specifically located in Column S of a worksheet.

How to Count Empty Cells in Column S with Office Scripts

Product: Spreadsheet Scripts/Macros
Device & OS: not provided
Scenario: Automating data analysis by writing a custom script to identify and tally the number of blank cells within a fixed dataset column.
Observed behavior: The user requires specific syntax or logic to accurately retrieve values from Column S and calculate the total count of empty strings.

Before you start

Ensure your workbook is saved. To optimize script performance, determine if you need to scan the entire column or just the rows that actually contain data (the used range).

Solution 1Recommended

Use an Office Script Array Filter to Count Blank Values

Extract values from the specific column into an array and use the JavaScript filter method to count empty strings efficiently.

This approach fetches the values of Column S into a two-dimensional array and evaluates each row's entry. It filters out everything except strings that strictly match an empty value.

For better performance in large datasets, it is highly recommended to limit the range to the used rows rather than evaluating the entire column 'S:S', which contains over a million rows.

Open the Code Editor

Navigate to the Automate tab on your spreadsheet ribbon and select 'New Script' to open the code editor panel.

Define the Target Range

Write code to get the active worksheet and identify the range for Column S. For example: `const sheet = workbook.getActiveWorksheet(); const values = sheet.getRange("S:S").getValues();`

Filter and Count Empty Cells

Add a filter logic to the array to count the empty strings: `const emptyCount = values.filter(row => row[0] === "").length;`

Log the Result

Use `console.log(emptyCount);` to display the final count in the execution logs, or assign it to another cell using `sheet.getRange("A1").setValue(emptyCount);`.

Use an Office Script Array Filter to Count Blank Values

Performance Optimization Tip: Instead of `sheet.getRange("S:S")`, try using `sheet.getUsedRange().getIntersection(sheet.getRange("S:S"))` to process only the active rows, which will drastically improve your script's execution speed.

Efficient Data Analysis with WPS

Count Blank Cells Instantly with WPS Office

Whether you prefer using built-in functions or customizing automations with JS Macros, WPS Spreadsheet offers seamless, robust solutions to handle large datasets effortlessly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you want to analyze.
2. Use the COUNTBLANK formula: For a no-code solution, select an empty cell outside your dataset and type `=COUNTBLANK(S:S)`.
3. Execute the calculation: Press Enter to instantly view the total number of empty cells in Column S.
4. Automate with JS Macros: Alternatively, go to the Tools tab, click 'JS Macro', and write a simple script loop to calculate and manipulate data programmatically across your document.

Fully compatible with Microsoft Excel formats and formulas like COUNTBLANK.Supports advanced JS Macros (JSA) for powerful, script-based automation.Lightweight, fast, and completely free for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my script count cells with spaces as not empty?

By default, a completely empty string `""` is treated differently from a string containing a space `" "`. If you want cells containing only spaces to be counted as blank, you should use the trim method in your logic, for example: `row[0].toString().trim() === ""`.

How can I speed up my script when analyzing entire columns?

Referencing an entire column (like `S:S`) forces the script to evaluate over 1 million rows. To speed up the process, restrict your script to the used range by utilizing `worksheet.getUsedRange()`, which only processes cells that contain actual data or formatting.

Is there a way to count empty cells without writing a script?

Yes. The easiest and most efficient alternative to writing a script is using the built-in spreadsheet formula `=COUNTBLANK(S:S)`. This automatically calculates the number of empty cells in that specific column and updates dynamically if the data changes.

Excel & Data Problems