VBA & Macro Problems

Hide Rows Containing Only Zeros with an Excel VBA Macro

Chanuka GeekiyanageChanuka Geekiyanage Nov 20, 2026 870 views

Question details

The user needs a VBA macro that hides rows only when every cell across the used columns contains zero.

Hide Rows Containing Only Zeros with an Excel VBA Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
Scan the used rows from the first used column through the last used column and hide rows consisting entirely of zeros.
Observed behavior
Rows containing a nonzero value should remain visible; the treatment of blank cells must be defined.
Before you start

Open the workbook in a desktop version of Excel that supports VBA, and make sure the worksheet allows rows to be hidden. Check the used range and decide whether blank cells should count as zeros; the macro below treats blanks as nonzero for this purpose.

Solution 1Recommended

Run a VBA macro to hide rows containing only numeric zeros

Use this macro when every cell across the worksheet's used columns must contain a numeric zero for its row to be hidden.

The macro scans every row in the worksheet's used range, including any header rows. Blanks, text, Boolean values, and errors prevent a row from being hidden; formulas that return numeric zero count as zeros.

Rows that do not meet the condition are made visible, including rows previously hidden for another reason. Formatting can extend Excel's used range, so extra blank columns may affect the result.

1
Open the VBA editor

In Excel, select Developer > Visual Basic. If the Developer tab is unavailable, enable it through Excel's ribbon customization settings.

2
Insert the macro

In the VBA editor, select Insert > Module and paste this code: Sub HideZeroRows() Dim ws As Worksheet Dim used As Range Dim r As Long, c As Long Dim lastRow As Long, lastCol As Long Dim allZero As Boolean Dim v As Variant Dim oldScreenUpdating As Boolean oldScreenUpdating = Application.ScreenUpdating On Error GoTo HandleError Application.ScreenUpdating = False Set ws = ActiveSheet Set used = ws.UsedRange lastRow = used.Row + used.Rows.Count - 1 lastCol = used.Column + used.Columns.Count - 1 For r = used.Row To lastRow allZero = True For c = used.Column To lastCol v = ws.Cells(r, c).Value2 If VarType(v) <> vbDouble Then allZero = False Exit For ElseIf v <> 0 Then allZero = False Exit For End If Next c ws.Rows(r).Hidden = allZero Next r CleanUp: Application.ScreenUpdating = oldScreenUpdating Exit Sub HandleError: MsgBox "The macro stopped: " & Err.Description, vbExclamation Resume CleanUp End Sub

3
Run it on the worksheet

Return to Excel, select the worksheet you want to process, and choose Developer > Macros. Select HideZeroRows and click Run.

4
Save the macro if needed

To keep the VBA code in the workbook, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

Run a VBA macro to hide rows containing only numeric zeros
Blank-cell behavior: A row containing both blanks and zeros stays visible. Change the macro's cell test only if your workbook's rules treat blanks as zeros.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files to make migration easier; this does not fix Excel's VBA behavior.

Review spreadsheet rows and values in WPS Spreadsheets.Open and edit compatible Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for an easier transition from Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Will a row with zeros and blank cells be hidden?

No. This macro requires every cell across the used columns to contain a numeric zero. A blank cell keeps the row visible.

Do formulas that return zero count as zeros?

Yes. The macro checks each cell's Value2, so a formula returning numeric zero counts as zero. A formula returning an empty string does not.

Will the macro affect rows I already hid?

Yes. It makes any scanned row that does not contain only zeros visible, even if that row was previously hidden manually.

Why does a row of zeros remain visible?

Check for blank cells, text, errors, or other nonnumeric values between the first and last used columns. Formatting can also extend the worksheet's used range into extra blank columns.

How do I keep the macro in my workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Run saved macros from Developer > Macros.