Row & Column Unhider: Show Every Hidden Row and Column in Your Inherited Workbook

TL;DR: You inherit a workbook where data seems incomplete — rows are missing, columns are invisible. Excel makes you find the hidden range and unhide one region at a time. This macro unhides every hidden row and every hidden column on every sheet in under a second and tells you exactly how many it found. Paste once, run forever.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with hidden rows or columns — any inherited workpaper or file from a client or prior preparer

What the macro does:

• Loops every visible sheet in ThisWorkbook
• Counts hidden rows and hidden columns within each sheet’s used range
• Unhides them all in a single pass
• Fixes zero-height rows and zero-width columns that Hidden = False doesn’t always restore
• Reports the per-sheet breakdown so you know exactly what was revealed

What the macro does NOT do:

• Does not unhide hidden sheets — that’s a different mechanism. Use the Unhide All Sheets macro for hidden tabs
• Does not remove AutoFilters. Use the Filter Remover macro for that
• Does not touch “very hidden” sheets — rows and columns on very-hidden sheets are inaccessible until the sheet itself is revealed
• Does not modify cell data, formulas, or formatting. Hidden rows and columns contain real data — the macro just makes them visible

Limitations:

• Works on ThisWorkbook — the workbook containing the macro. Store in your Personal Macro Workbook (PERSONAL.XLSB) to run on any open file
• Rows hidden outside the used range (e.g., row 50,000 when data ends at 200) are not counted or affected. These are in the “empty zone” of the sheet and don’t hide any data
• Zero-height rows set via RowHeight = 0 (not .Hidden = True) are fixed by the macro’s second pass. Excel normally sets .Hidden = True when you right-click → Hide, but some VBA code sets height to zero directly

#The Macro

Option Explicit

Sub UnhideAllRowsColumns()
    ' ── Row & Column Unhider ───────────────────────────
    ' Loops every sheet, counts hidden rows and columns
    ' within the used range, then unhides them all.
    ' Fixes zero-height rows and zero-width columns
    ' (which Hidden = False doesn't always restore).
    ' Reports a per-sheet breakdown.
    '
    ' Zero input. Zero configuration. Just run.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim totalRows As Long, totalCols As Long
    Dim rowCount As Long, colCount As Long
    Dim r As Long, c As Long
    Dim firstRow As Long, lastRow As Long
    Dim firstCol As Long, lastCol As Long
    Dim detail As String, sheetCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    totalRows = 0
    totalCols = 0
    sheetCount = 0
    detail = ""

    For Each ws In ThisWorkbook.Worksheets
        rowCount = 0
        colCount = 0

        ' Find the used range boundaries
        If Not IsEmpty(ws.UsedRange) Then
            firstRow = ws.UsedRange.Row
            lastRow = ws.UsedRange.Rows.Count + firstRow - 1
            firstCol = ws.UsedRange.Column
            lastCol = ws.UsedRange.Columns.Count + firstCol - 1

            ' Count hidden rows
            For r = firstRow To lastRow
                If ws.Rows(r).Hidden Then
                    rowCount = rowCount + 1
                End If
            Next r

            ' Count hidden columns
            For c = firstCol To lastCol
                If ws.Columns(c).Hidden Then
                    colCount = colCount + 1
                End If
            Next c
        End If

        ' Unhide and fix zero-dimension cells
        If rowCount > 0 Or colCount > 0 Then
            ws.Rows.Hidden = False
            ws.Columns.Hidden = False

            ' Restore zero-height rows
            For r = firstRow To lastRow
                If ws.Rows(r).RowHeight = 0 Then
                    ws.Rows(r).RowHeight = ws.StandardHeight
                End If
            Next r

            ' Restore zero-width columns
            For c = firstCol To lastCol
                If ws.Columns(c).ColumnWidth = 0 Then
                    ws.Columns(c).ColumnWidth = ws.StandardWidth
                End If
            Next c

            totalRows = totalRows + rowCount
            totalCols = totalCols + colCount
            sheetCount = sheetCount + 1
            detail = detail & "  • " & ws.Name & " (" & _
                     rowCount & " row, " & colCount & " col)" & vbCrLf
        End If
    Next ws

    ' ── Report results ─────────────────────────────────
    If totalRows = 0 And totalCols = 0 Then
        MsgBox "No hidden rows or columns found.", _
               vbInformation, "All Clear"
    Else
        MsgBox "Unhid " & totalRows & " row(s) and " & _
               totalCols & " column(s) across " & sheetCount & _
               " sheet(s):" & vbCrLf & vbCrLf & detail, _
               vbInformation, "Done"
    End If

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two passes: count first, then unhide

The macro separates counting from unhiding for a reason. If you unhide rows as you find them, you can’t report an accurate before-count — you’ve already changed the state. The counting pass is read-only and collects all the information first:

For r = firstRow To lastRow
    If ws.Rows(r).Hidden Then
        rowCount = rowCount + 1
    End If
Next r

Only after every hidden row and column across every sheet is counted does the macro start unhiding. The per-sheet breakdown in the MsgBox tells you exactly what was found before any changes were made.

#Why ws.UsedRange not ws.Rows.Count

Excel sheets have 1,048,576 rows in modern versions. Iterating all of them would take minutes. The macro only checks rows within the sheet’s UsedRange — the smallest rectangle that contains all data, formatting, and formulas:

firstRow = ws.UsedRange.Row
lastRow = ws.UsedRange.Rows.Count + firstRow - 1

If your data occupies rows 1 through 200, the macro only checks 200 rows, not a million. Same logic applies to columns. The UsedRange property is Excel’s internal map of “where stuff lives on this sheet.”

There’s one edge case: if a user hid rows within the used range and then deleted the data, UsedRange might still extend to those rows. The macro counts them anyway — better to report “5 rows unhid” on a sheet with no visible gaps than to miss something. The MsgBox shows the per-sheet breakdown so you can verify.

#Hidden vs. zero-height: two mechanisms, one fix

Excel hides rows in two ways, and they don’t always work together:

1. Regular hidden (ws.Rows(r).Hidden = True): What happens when you right-click a row header → Hide. Setting .Hidden = False unhides it.

2. Zero-height (ws.Rows(r).RowHeight = 0): Some VBA code sets the row height to zero to hide content without triggering the .Hidden property. Setting .Hidden = False does NOT restore zero-height rows.

The macro handles both. The first unhide pass uses .Hidden = False to catch standard hidden rows and columns. The second pass checks every row and column in the used range for zero height or width and resets them:

For r = firstRow To lastRow
    If ws.Rows(r).RowHeight = 0 Then
        ws.Rows(r).RowHeight = ws.StandardHeight
    End If
Next r

ws.StandardHeight returns the default row height for the sheet (usually 15 points). ws.StandardWidth does the same for columns. Using these values instead of hardcoded numbers means the macro respects the sheet’s existing default sizing.

#Why no Calculation toggle

Most macros on this blog toggle Application.Calculation = xlCalculationManual to avoid triggering a recalculation cascade on every cell write. This macro doesn’t write to any cells — it only changes row and column visibility properties and dimensions. No cell values change, so no formulas recalculate. The Calculation toggle would add two lines for zero benefit.

Application.ScreenUpdating = False is still essential. Without it, Excel redraws the sheet after every row and column is unhidden, causing visible flickering as the grid redraws. For a sheet with 30 hidden rows, the screen flashes 30 times.

#The message box is your audit trail

A silent macro that unhides rows is dangerous — you run it and might not notice anything changed if you were looking at the top of a sheet. The message box tells you exactly what happened:

Unhid 23 row(s) and 8 column(s) across 4 sheet(s):
  • Fixed Assets (6 row, 3 col)
  • Depreciation (10 row, 5 col)
  • Sched-A Income (5 row, 0 col)
  • JE Log (2 row, 0 col)

You can immediately spot-check: if Sched-A Income is listed with 5 hidden rows but the data starts on row 8, you know rows 3–7 are now visible. If a sheet you thought was complete shows up with 10 hidden rows, you know there’s work buried that you need to review.

#A For Each loop with purpose

The macro uses For Each ws In ThisWorkbook.Worksheets to visit every sheet. This is simpler and more reliable than iterating by sheet index (For i = 1 To Worksheets.Count). The For Each pattern handles sheet additions, deletions, and renames without needing to track index numbers. It’s also the convention used by every macro on this blog — readers who paste multiple macros will see consistent patterns.

#Adapt It