VBA Hide Columns and Rows in Excel — Hidden Isn't Deleted (and Why Your Totals Don't Change)

TL;DR — Hiding is the gentlest of the three structural edits, and the most misunderstood. Unlike delete (which removes rows) or insert (which adds them), hiding changes nothing about the data — it sets a column's or row's display size to zero. The cells are still there, still in every SUM, still copied when you copy the range, still fully editable by code. .Hidden = True lives on .EntireColumn / .EntireRow, not on a stray cell. The trap that catches everyone: hiding a column does not remove it from a total — SUM(B:B) still adds hidden column B. Hide for presentation; if you need something gone from a calculation, that's delete, filter, or SUBTOTAL — not hide.

' Hide helper columns B:D; hide any row whose Status is "Done".
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Report")

ws.Columns("B:D").Hidden = True                 ' whole columns, one statement
ws.Rows("2:1000").EntireRow.Hidden = False      ' start from all-visible, then hide selectively
Dim i As Long
For i = 2 To 1000
    ws.Rows(i).Hidden = (ws.Cells(i, "Status").Value = "Done")
Next i

Hiding looks like the simplest of the three operations, and mechanically it is — one Boolean property. What makes it worth a guide is the mental model, because "hide" quietly means something different from what most people assume. They reach for it to exclude data — from a view, from a total, from a copy — and hiding does only the first of those. Getting that straight is the whole battle; the syntax takes about a line.

What you'll learn

The mental model — hiding resizes to zero, it doesn't remove or exclude the data
Why .Hidden belongs to .EntireColumn and .EntireRow
The trap that catches everyone — hidden cells still count in formulas and copies
Unhiding — the forgotten half, and the line that rescues a stuck sheet
Manually hidden rows versus AutoFilter-hidden rows when you loop

The mental model: hidden is zero-width, not gone

Deleting removes a row; inserting adds one. Hiding does neither — it takes an existing row or column and sets its height or width to zero so it doesn't paint on screen. Everything else is unchanged. The data sits exactly where it was, at exactly the same address; Range("B2") still reads and writes the value in a hidden column B without a hitch. A hidden row is not a deleted row wearing a disguise — it's a fully present row you've simply told Excel not to draw.

That's why hiding is the non-structural structural edit: it changes the display, not the grid. Row 6 is still row 6 when it's hidden; nothing slid up, nothing shifted down, so there's no loop-direction trap here — you can hide and unhide in a plain top-down loop all day. The subtlety isn't in how you hide; it's in believing the data went somewhere. It didn't.

The rule of syntax: .Hidden lives on the whole column or row

Hidden is a property of an entire column or an entire row — not of an arbitrary cell or block. You can't hide B2:B10; you hide the columns or rows those cells belong to. So you either address the whole column/row directly, or promote a cell range with .EntireColumn / .EntireRow:

ws.Columns("B").Hidden = True                 ' hide column B
ws.Columns("B:D").Hidden = True               ' hide B, C and D
ws.Rows("10:20").Hidden = True                ' hide rows 10 through 20
ws.Range("B2:D2").EntireColumn.Hidden = True  ' promote a cell range to its full columns

Try to set .Hidden on a partial range and you'll either hit an error or hide more than you meant, because the property simply isn't defined at cell granularity. .EntireColumn and .EntireRow are the same promotions you use for delete and insert — the recurring move that takes you from "these cells" to "the whole column/row they sit in."

The trap that catches everyone: hidden cells still count

Here is the belief that causes real bugs: hiding a column removes it from my totals. It does not. Hiding is a display setting; it has no effect on calculation. =SUM(B:B) includes hidden column B. =SUM(A2:A100) includes every hidden row in that span. Copy a range that spans hidden columns and the hidden values come along into the paste. Chart a hidden series and — unless you tell the chart otherwise — it may vanish, or it may not. Hiding changes what a human sees, and almost nothing about what a formula or a copy does.

So if your actual goal is "leave these values out of the total," hiding is the wrong tool, and reaching for it produces a report whose numbers don't match its visible rows. The right tools:

SUBTOTAL(109, ...) or AGGREGATE — these do ignore rows hidden by a filter (and SUBTOTAL(109) also ignores manually hidden rows), so a filtered total updates as you filter.
AutoFilter — hides and excludes from SUBTOTAL, the honest way to "show and total only matching rows."
Delete — if the data genuinely shouldn't exist, remove it (see VBA Delete Rows).

Use .Hidden when the data should stay, stay in the math, and just not clutter the view — helper columns behind a dashboard, working rows beside a printed summary. That's what hiding is for.

Unhiding: the forgotten half and the rescue line

Every hide needs a matching unhide, and the bug is forgetting that a wider selection must do the unhiding. If column B is hidden and you only ever touch Columns("B"), a user who never runs your macro can't get it back by hand easily — they have to select across the gap. In code, the reliable rescue is to unhide across a generous range, or the whole sheet:

ws.Columns("A:Z").Hidden = False   ' unhide any hidden column in A:Z
ws.Rows("1:1000").Hidden = False   ' unhide any hidden row in that span
ws.Cells.EntireColumn.Hidden = False   ' nuclear option: unhide EVERY column on the sheet
ws.Cells.EntireRow.Hidden = False       ' ...and every row

ws.Cells.EntireColumn.Hidden = False is the line to remember — it unhides everything, no matter which columns were hidden or by whom. One more distinction worth knowing: setting Columns("B").ColumnWidth = 0 also makes a column invisible, but it is not the same as .Hidden = True — .Hidden is the real property, it survives round-trips cleanly, and it's what "Unhide" in the UI toggles. Prefer .Hidden; treat a literal width of zero as a bug to fix, not a technique.

Hidden rows vs filtered rows: the same flag, different when you loop

Both a manually hidden row and an AutoFilter-hidden row report .Hidden = True, so a loop that tests If ws.Rows(i).Hidden can't tell them apart — which matters when you want to act on "only the rows the user can see." The clean way to grab visible cells is SpecialCells:

' Loop only the currently visible rows (skips both hidden and filtered-out rows).
Dim rng As Range, area As Range
On Error Resume Next
Set rng = ws.Range("A2:A1000").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each area In rng.Areas          ' visible cells come back as multiple blocks
        ' ... process area ...
    Next area
End If

Two things to internalise. First, SpecialCells(xlCellTypeVisible) returns the visible cells as multiple areas (each unbroken run of visible rows is one area), so iterate .Areas, not a single contiguous block. Second, a plain For i = 2 To 1000 loop visits hidden and filtered rows just like any other — hiding doesn't remove a row from iteration, only from view. If you mean "the rows on screen," ask for visible cells explicitly; don't assume the loop skips the hidden ones.

How ExcelMaster helps

Hiding trips people not on syntax but on expectation: totals that stubbornly include hidden columns, a sheet nobody can un-stick because the unhide range was too narrow, a loop that processes filtered-out rows because .Hidden didn't mean what they thought. The fixes are all small and all easy to miss.

ExcelMaster lets you describe the intent — "hide the helper columns B to D," "hide every row marked Done," "show only visible rows in the total." It writes .EntireColumn.Hidden / .EntireRow.Hidden correctly, reaches for SUBTOTAL or SpecialCells(xlCellTypeVisible) when you actually mean "exclude from the math" rather than merely "hide," and gives you an unhide-everything path so no sheet ends up stuck. You keep the workbook and the code; you skip the surprise of a total that ignores what your eyes are telling you.

Frequently asked questions

How do I hide a column in VBA?

Set .Hidden = True on the whole column: ws.Columns("B").Hidden = True, or ws.Columns("B:D").Hidden = True for several. From a cell range, promote it first: ws.Range("B2:D2").EntireColumn.Hidden = True. .Hidden is a property of an entire column or row, so you can't apply it to a partial range.

Does hiding a column remove it from a SUM in Excel?

No. Hiding only changes the display — =SUM(B:B) still includes hidden column B, and copying a range still copies hidden values. To exclude cells from a total, use SUBTOTAL(109, ...) or AGGREGATE (which ignore filtered, and with 109 also manually hidden, rows), apply an AutoFilter, or delete the data. Hiding is presentation, not calculation.

How do I unhide all columns and rows in VBA?

Use ws.Cells.EntireColumn.Hidden = False to unhide every column and ws.Cells.EntireRow.Hidden = False to unhide every row. Because it targets the whole sheet, it works no matter which columns or rows were hidden — the reliable rescue for a sheet where you can't find the hidden ones by hand.

How do I hide rows based on a cell value?

Loop the rows and set .Hidden from a test: ws.Rows(i).Hidden = (ws.Cells(i, "C").Value = "Done"). Because hiding doesn't shift the grid, a plain top-down loop is fine — no need to loop backwards as you would when deleting. Start from an all-visible state so previously hidden rows reset.

Why does my loop still process hidden rows?

Because hiding a row doesn't remove it from iteration — a For i = 2 To 1000 loop visits hidden and filtered rows the same as visible ones. To act only on what's on screen, get the visible cells with Range(...).SpecialCells(xlCellTypeVisible) and iterate its .Areas; a manual .Hidden test can't distinguish filter-hidden from manually hidden rows.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-07.