Hide Rows Containing Only Zeros with an Excel VBA Macro
Question details
The user needs a VBA macro that hides rows only when every cell across the used columns contains zero.

- 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.
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.
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.
In Excel, select Developer > Visual Basic. If the Developer tab is unavailable, enable it through Excel's ribbon customization settings.
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
Return to Excel, select the worksheet you want to process, and choose Developer > Macros. Select HideZeroRows and click Run.
To keep the VBA code in the workbook, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

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.

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.




