VBA Range.Hidden: How to Check Hidden Rows and Columns

In Excel VBA, Range.Hidden checks or sets whether entire rows or columns are hidden; it does not hide individual cells. Use a whole-row or whole-column range, such as Rows("5:5").Hidden = True to hide row 5, or read the property to check its state.

What the Range.Hidden property means

Hidden is a read/write property of Excel’s Range object. It applies to rows and columns, not to selected cells within a row or column. A range such as A5:C5 covers only part of row 5, so use EntireRow to refer to the complete row. Likewise, use EntireColumn when the starting range covers only some cells in a column.

The property is useful for automating ordinary hide and unhide actions, checking layout before a report is printed, or making a worksheet easier to navigate. For example, setting the property to True hides the relevant complete row or column. Setting it to False makes that row or column visible again. It does not delete data, change formulas, or protect the contents from being viewed by someone who can unhide the rows or columns.

Because the property is read/write, the same syntax can be used to inspect or change visibility. The main distinction is whether the range refers to full rows or full columns. Using EntireRow or EntireColumn makes that intent explicit and helps avoid errors caused by trying to hide a partial-cell range.

How to hide rows and columns with VBA

For a one-off command in the Immediate window or a procedure, refer to the worksheet and set the property on its rows or columns. These examples use a worksheet variable so that the target sheet is unambiguous:

Sub HideSelectedRowsAndColumns()
    Dim ws As Worksheet

    ' Point to the worksheet that should change.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Rows("5:7").Hidden = True
    ws.Columns("B:D").Hidden = True
End Sub

The first assignment hides rows 5 through 7; the second hides columns B through D. Replace Sheet1 with the actual worksheet tab name. The name in the code must match the sheet name exactly. If the macro is stored in a different workbook from the data, ThisWorkbook means the workbook containing the macro, not necessarily the workbook currently active on screen.

You can also start with a cell or a partial range and expand it to the full row or column. For example, ws.Range("A12").EntireRow.Hidden = True hides row 12, and ws.Range("C1").EntireColumn.Hidden = True hides column C. This is often convenient when code already has a cell reference. Before running a macro that changes a workbook, save a separate copy: VBA macros cannot be reversed with Ctrl+Z.

How to check whether a range is hidden

Read the property from a complete row or column. A range containing several rows or columns can have a uniform state, or it can contain a mixture of hidden and visible rows or columns. Since Hidden returns a Variant, a mixed range can return Null. Check for that condition before treating the result as a simple True or False.

Sub CheckRowVisibility()
    Dim ws As Worksheet
    Dim visibility As Variant

    ' Use a whole-row range for the check.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    visibility = ws.Rows("2:10").Hidden

    If IsNull(visibility) Then
        Debug.Print "The rows have mixed visibility."
    ElseIf CBool(visibility) Then
        Debug.Print "Every row in the range is hidden."
    Else
        Debug.Print "Every row in the range is visible."
    End If
End Sub

To check just one row, use a one-row reference such as ws.Rows(5).Hidden. To check one column, use ws.Columns("C").Hidden. A single row or column gives a straightforward True or False result. With multiple rows or columns, do not assume that the result describes each member individually; inspect rows or columns separately when you need a detailed list.

The example writes its result to the Immediate window, which is available in the VBA editor. That keeps the check separate from worksheet data. If you want to display a message on the sheet or in a dialog, add that behavior deliberately; a visibility check by itself does not display anything to the user.

Using EntireRow and EntireColumn correctly

EntireRow and EntireColumn are range properties that return the full rows or columns intersecting the original range. For instance, a range such as B4:D6 intersects rows 4, 5, and 6, so Range("B4:D6").EntireRow.Hidden = True hides all three rows. It does not hide only the cells from B through D. The same principle applies to columns: Range("B4:D6").EntireColumn.Hidden = True hides columns B, C, and D from top to bottom.

This can be helpful when the starting range is found dynamically, but it can also hide more than a new VBA user expects. Before using EntireRow or EntireColumn, identify which row numbers or column letters the source range intersects. If the range spans many rows, the whole span will be affected. For a clear, limited operation, specify the intended rows or columns directly, such as ws.Rows("4:6").

Fully qualifying the range matters too. A reference like Rows("5:7") without a worksheet qualifier acts on the active sheet. If the user changes sheets before the macro runs, the wrong worksheet could be affected. Using ws.Rows("5:7"), where ws is explicitly assigned to the intended sheet, makes the target predictable.

Hidden rows, filters, and grouped outlines

A row that is not currently visible is not always hidden for the same reason. A person or macro can manually hide a row, an AutoFilter can exclude it from the displayed results, and a grouped outline can collapse rows. These features affect what the user sees in different ways. If your goal is to identify rows visible after filtering, checking Hidden alone may not answer the business question: it reports row or column hidden state, while the filter and outline determine how the worksheet is currently displayed.

For filter-aware work, decide whether you need to inspect the filter criteria, the rows visible at that moment, or the manually hidden state. Those are different tasks and may call for different VBA approaches. For example, code that copies currently displayed records should be designed around visible cells rather than assuming that a row is visible simply because it was not intentionally hidden by a user. Test the procedure with the worksheet’s real filter and outline setup.

Similarly, collapsing a group is not the same as deleting its detail rows. Expanding the outline can show the data again. Hiding a row with Hidden = True also leaves its values and formulas in the workbook. Neither action is a privacy feature; use proper access controls or remove sensitive data if it must not be available to workbook users.

How to unhide rows and columns

Set the same property to False to show rows or columns. You can unhide a specific range by naming it, or unhide all rows or columns on the target worksheet. For example, ws.Rows("5:7").Hidden = False shows rows 5 through 7, while ws.Columns("B:D").Hidden = False shows columns B through D.

Showing a broader range can be useful when a worksheet has many hidden items and you want to restore a complete view. It can also undo deliberate layout choices, so choose the scope carefully. If a row remains out of sight after setting Hidden to False, check whether a filter is still excluding it or an outline is still collapsed. A sheet’s display can be affected by more than one feature at once.

In the Excel interface on Windows, you can also select the rows or columns around a hidden section, right-click the selection, and choose Unhide. For a VBA-driven process, explicitly setting the intended row or column range to False is generally easier to repeat and document. On Mac, the worksheet capability is the same, although ribbon and context-menu presentation can vary slightly by Excel release.

Common problems and safe practices

Trying to hide partial cells: use a whole-row or whole-column reference, or add EntireRow or EntireColumn.
Getting an error on a protected sheet: worksheet protection may prevent formatting rows or columns. Review the protection settings and permissions before the macro runs; do not remove protection without authorization.
Changing the wrong sheet: qualify the range with a worksheet variable rather than relying on the active sheet.
Using a mixed range as a Boolean: test IsNull before converting the result or using it in a True/False decision.
Expecting Ctrl+Z to undo a macro: Excel clears the ordinary undo history when VBA changes the workbook. Save a copy first and make the macro’s scope as narrow as practical.

Excel for Microsoft 365 on Windows supports VBA in the desktop application. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros; open the file in desktop Excel to use this code. Save workbooks containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro. On Mac, VBA is also available in desktop Excel, but keyboard shortcuts for opening the editor can depend on the keyboard’s function-key settings.

Before distributing a macro, confirm its behavior on a copy with representative hidden rows, filters, and protection settings. This is especially important when a procedure uses EntireRow or EntireColumn, since it affects whole worksheet rows or columns rather than only the cells named in the original range.

Frequently Asked Questions

What does Range.Hidden return in VBA?
Range.Hidden returns a Variant indicating whether the referenced whole rows or columns are hidden. For a range containing a mixture of hidden and visible rows or columns, the result can be Null, so check with IsNull before treating it as True or False.

Can VBA hide only one cell?
No. Hidden controls complete rows or columns, not individual cells. To conceal a cell’s displayed value without hiding its row or column, consider a different presentation method, while remembering that formatting is not a security measure.

How do I hide a row based on a cell reference?
Use the referenced cell’s EntireRow property and set Hidden to True. For example, a reference to a cell in row 12 can target that complete row with Range("A12").EntireRow.Hidden = True; qualify the range with the correct worksheet in a procedure.

Why does Hidden return Null?
The range may contain both hidden and visible rows or columns. Read one row or column at a time when you need a separate answer for each, or handle the mixed result with IsNull.

Does hiding a row delete its contents?
No. Hiding changes the worksheet display, not the row’s data. The values and formulas remain in the workbook and can become visible again if the row is unhidden.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.