VBA & Macro Problems

How to Hide Zero-Value Rows in an Excel Table Using VBA

Guest Writer Oct 9, 2026 871 views

Question details

The user wants to use a VBA macro to hide rows within a structured Excel Table if the cells in a specific column contain a zero value.

How to Hide Zero-Value Rows in an Excel Table Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up the presentation of an Excel Table by hiding rows with zero values without deleting the underlying data.
Observed behavior: Standard VBA worksheet range references often fail to adapt to dynamic Excel Tables, requiring the specific use of the table's DataBodyRange property to reliably target and hide the correct rows.

Before you start

Ensure your Excel file is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the VBA code can execute.

Solution 1Recommended

Use a Standard Module to Hide Zero-Value Rows Manually

Create a macro that loops through the table's DataBodyRange to hide rows containing zeros on demand.

When working with an existing Excel Table, standard range references might not resize dynamically. Referencing the DataBodyRange of a specific ListColumn ensures your macro only processes the actual data rows within your table.

Open the Visual Basic Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a Standard Module

Click on 'Insert' in the top menu and select 'Module'. This will create a new blank window for your code.

Paste the VBA Code

Type or paste the following code: Sub HideZeros() Dim rng As Range, cel As Range Set rng = ActiveSheet.ListObjects("Table1").ListColumns("Sample").DataBodyRange For Each cel In rng cel.EntireRow.Hidden = (cel.Value = 0) Next cel End Sub

Run the Macro

Close the editor and return to your worksheet. Press ALT + F8, select 'HideZeros', and click 'Run' to execute the macro.

Use a Standard Module to Hide Zero-Value Rows Manually

Update Placeholders: Make sure to replace "Table1" with your actual table name and "Sample" with the exact header name of the column you want to check for zero values.

Solution 2

Automate Row Hiding with the Worksheet_Change Event

Set up an automatic trigger that hides rows the moment a value in the specified table column is changed to zero.

Advanced Data Management in WPS Spreadsheet

Easily Manage Macros and Tables with WPS Office

WPS Spreadsheet provides comprehensive support for VBA macros and structured data tables, allowing you to seamlessly run scripts that automate formatting, like hiding zero-value rows.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access Macro Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Macros'.
3. Open the Visual Basic Editor: Select 'Visual Basic Editor' to write or paste your table-referencing VBA code.
4. Execute the Code: Run your macro directly from the editor to instantly hide the zero-value rows in your table.

Fully compatible with Microsoft Excel .xlsx and .xlsm formatsBuilt-in Visual Basic Editor to create, edit, and troubleshoot macrosLightweight architecture ensures fast macro execution even on large tablesFree alternative to easily manage complex data automations

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA code returning an 'Out of Range' error?

This error usually occurs when the macro cannot find the specified Table or Column name. Double-check your spelling inside the ListObjects("Table1") and ListColumns("Sample") arguments to ensure they match your workbook exactly.

How do I find the name of my Excel Table?

Click anywhere inside your table, then navigate to the 'Table Design' tab on the ribbon. You will see the 'Table Name' displayed in a text box on the far left side of the menu.

How can I unhide the rows that the macro hid?

You can select the rows above and below the hidden area, right-click the row numbers, and select 'Unhide'. Alternatively, you can run a one-line macro: ActiveSheet.Cells.EntireRow.Hidden = False.

Can I hide rows across multiple columns checking for zeros?

Yes. You would need to expand the VBA logic to check additional columns within the DataBodyRange, usually by modifying the cell evaluation step to look at adjacent offset cells before setting the EntireRow.Hidden property to True.

Excel & Data Problems