Macro to Hide and Unhide Rows and Columns in Excel

These VBA macros hide the selected rows or columns, hide rows based on a value, and unhide everything on one sheet or every sheet.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Hide the selected rows or columns

Sub HideSelectedRows()
    Selection.EntireRow.Hidden = True
End Sub

Sub HideSelectedColumns()
    Selection.EntireColumn.Hidden = True
End Sub

Hide rows where a value is zero

This hides every row where column D is 0, such as products with no sales:

Sub HideZeroRows()
    Dim c As Range
    For Each c In Range("D2", Cells(Rows.Count, "D").End(xlUp))
        If IsNumeric(c.Value) Then c.EntireRow.Hidden = (c.Value = 0)
    Next c
End Sub

Rows are hidden when the value is 0 and shown again when it isn’t, so you can run it again after the data changes. Empty cells count as 0. Change "D" to test another column.

Unhide all rows and columns

Sub UnhideAllRowsColumns()
    Cells.EntireRow.Hidden = False
    Cells.EntireColumn.Hidden = False
End Sub

Unhide on every sheet

Sub UnhideAllOnEverySheet()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Cells.EntireRow.Hidden = False
        ws.Cells.EntireColumn.Hidden = False
    Next ws
End Sub

Things to know

• Rows hidden by a filter are different: clear the filter with Data → Clear instead.
• Hidden rows are still included in SUM. Use SUBTOTAL with 109 to total only visible rows.
• If the sheet is protected, unprotect it first.

Related macros