VBA & Macro Problems

How to Automatically Hide Blank Cells in Excel (VBA & Power Query)

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs a fully automated method to hide individual blank cells—without hiding entire rows—in an Excel workbook that dynamically receives data from Microsoft Forms.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a shared Excel workbook populated with varying rows and columns of Microsoft Forms responses.
Observed behavior: Blank cells appear dynamically as new form data arrives. The user wants these empty cells hidden automatically without manually applying filters or relying on additional helper sheets.

Before you start

Since hiding individual cells without hiding entire rows is structurally impossible in standard spreadsheet grids, you must evaluate whether extracting data to a clean table or using a macro to physically shift cells is acceptable for your shared workbook.

Solution 1Recommended

Use Power Query to Filter Out Blank Cells

Power Query is the most robust method for handling dynamic data from Microsoft Forms, allowing you to create a clean, automatic table containing only populated values.

Load data into Power Query

Select your data range and go to the 'Data' tab, then click 'From Table/Range'.

Filter out the blank values

In the Power Query Editor, click the dropdown arrow on the headers of the columns containing blanks, and uncheck 'null' or '(Blank)'.

Output the clean table

Click 'Close & Load' to generate a new worksheet with your clean data.

Refresh data on demand

Whenever new responses arrive from Microsoft Forms, right-click the new table and select 'Refresh' (or click 'Refresh All' in the Data tab) to update the view automatically.

Solution 2

Extract Populated Data Using the FILTER Function

If you prefer not to use Power Query, the FILTER function can dynamically pull non-blank data into a new range without requiring manual refreshes.

Solution 3

Apply a VBA Macro to Automate Cleanup on Opening

If you absolutely cannot use helper sheets, a VBA macro can be triggered upon opening the workbook to hide rows or shift blank cells.

Manage Dynamic Spreadsheet Data Seamlessly with WPS Office

WPS Spreadsheet offers full support for dynamic array functions like FILTER and includes advanced VBA macro capabilities in its premium versions, making it easy to automate the cleanup of blank cells in dynamic datasets.

1. Open your workbook: Launch WPS Spreadsheet and open your dynamic Microsoft Forms data file.
2. Apply formulas: Use the built-in FILTER function to dynamically separate blank cells into a clean view.
3. Run automation: Navigate to the Developer tab to access the VBA editor and run custom automation scripts.

Fully compatible with Microsoft Excel formats (.xlsx and .xlsm).Built-in support for dynamic array formulas like FILTER to manage blank cells.Powerful VBA editor available to execute and automate your custom macros.Lightweight, fast, and highly intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I hide just one blank cell without hiding the entire row or column?

No. Spreadsheet applications operate on a strict grid system. You can only hide entire rows or columns. To remove specific gaps in the middle of your data, you must either delete the blank cells and shift the remaining cells up, or use formulas to extract the populated data to a new location.

Does Power Query update automatically when new Microsoft Forms data is submitted?

Power Query does not refresh in real-time completely on its own. You need to click 'Refresh All' in the Data tab when you open the file, or you can configure a simple VBA script (or connection property) to refresh the query automatically upon opening the workbook.

Why is my dynamic array FILTER formula returning a #SPILL! error?

A #SPILL! error occurs when the intended output range for the FILTER function is blocked by existing text, formatting, or data. Ensure the cells immediately below and to the right of your formula are completely empty so the extracted data has room to populate.

Excel & Data Problems