In Excel VBA, you can hide the entire rows or columns that contain a range, but you cannot hide just an arbitrary block of cells while leaving the rest of those rows and columns visible. For example, use Rows("5:8").Hidden = True to hide rows 5 through 8, or use Range("B2:D10").EntireColumn.Hidden = True to hide columns B through D.
What “hide a range” means in Excel
Excel’s built-in Hide command works on whole rows and whole columns. A rectangular selection such as B2:D10 does not become an invisible rectangle: if you set its entire rows as hidden, all of rows 2 through 10 disappear, including cells outside columns B:D. If you set its entire columns as hidden, all of columns B through D disappear from top to bottom.
This distinction matters when you are designing a report or automating a worksheet. Hiding entire rows is useful for removing detail records, helper rows, or sections that should not appear in a printout. Hiding entire columns works well for intermediate calculations or fields not needed by the current audience. If only a few cells should be concealed, consider a different approach, described below.
Hidden rows and columns remain in the worksheet. Their values, formulas, and formatting are not deleted, and formulas elsewhere can still refer to them. Hiding is a display choice, not a way to erase data or restrict access.
Hide rows or columns with VBA
The Hidden property is the direct way to control row or column visibility. Set it to True to hide the target and False to show it again. Qualify the target with a worksheet variable so the macro does not accidentally change whichever sheet happens to be active.
Option Explicit
Sub HideReportRows()
Dim ws As Worksheet
' Change the sheet name to match your workbook.
Set ws = ThisWorkbook.Worksheets("Report")
ws.Rows("5:8").Hidden = True
End Sub
To hide columns, use the same property on the worksheet’s Columns collection. You can also begin with a cell range and ask Excel for its entire columns or rows:
Option Explicit
Sub HideReportColumns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Report")
ws.Columns("B:D").Hidden = True
' This also hides the complete rows containing A12:F12.
ws.Range("A12:F12").EntireRow.Hidden = True
End Sub
The first assignment hides columns B, C, and D. The second hides row 12, not merely cells A12:F12. If you want to hide a range’s columns instead, use ws.Range("A12:F12").EntireColumn.Hidden = True; that hides every column touched by the range.
Unhide a range
To reverse the change in code, set Hidden to False for the same rows or columns. The target sheet must be the one on which the rows or columns were hidden.
Option Explicit
Sub ShowReportRowsAndColumns()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Report")
ws.Rows("5:8").Hidden = False
ws.Columns("B:D").Hidden = False
End Sub
There is a useful practical difference between explicitly showing a known range and showing every row or column on a sheet. Assigning False to all rows can reveal rows another user intentionally hid. If your macro owns only rows 5 through 8, unhide only those rows. If the goal is to restore the whole worksheet, you can use ws.Rows.Hidden = False and ws.Columns.Hidden = False, but understand that those statements clear all row and column hiding on that sheet.
Excel’s interface can also show or hide selections: select the row or column headers, then use the Home tab’s Format menu and choose the relevant Hide & Unhide command. In VBA, assigning the property is easier to repeat consistently and can be included in a larger task.
Hide rows based on worksheet data
A macro can decide which rows to hide by checking a value, such as a status in column A. The example below hides rows where column A is blank. It checks only rows 2 through 100, so adjust the range to match the data and keep the header row out of the test.
Option Explicit
Sub HideRowsWithBlankStatus()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Report")
For Each cell In ws.Range("A2:A100")
If Len(cell.Value) = 0 Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub
This approach is straightforward for a modest, fixed-size range. It does not automatically restore rows that now contain data; add an explicit reset first if each run should start from a clean display. For a large or changing dataset, Excel’s AutoFilter is often a better fit: filtering is designed to show records that meet criteria and is easier for users to adjust than a macro that permanently sets row visibility.
If your data grows, calculate the last relevant row rather than hard-coding 100. For example, when column A is consistently populated for each record, VBA can use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row to find its last used row. This method depends on the chosen column being a reliable indicator of the data’s extent; blanks within that column do not stop the search from finding the last nonblank cell.
Can VBA hide only selected cells?
Not with the row or column Hidden property. Excel has no ordinary worksheet setting that makes an arbitrary range such as B2:D10 invisible while leaving the surrounding cells in the same rows and columns untouched. Setting row height to zero or column width to zero still hides a whole row or column, so it does not solve that limitation.
If the aim is simply to keep certain values out of view in a presentation, possible alternatives include moving the values to a separate worksheet, using a filtered table, or changing the report layout so that the sensitive or intermediate fields are not displayed. Formatting cells to use a font color that matches the background may make text less visible, but it is fragile: users can change formatting, select the cells, or inspect the formula bar. It is not a confidentiality measure.
Worksheet protection can restrict editing, but it does not make hidden information secure. A hidden row can often be unhidden, and protection is not a substitute for access controls on sensitive data. If people should not have access to particular information, do not distribute that information in the workbook they receive.
Use grouping when users should expand details
For reports where readers may want to reveal details on demand, outline grouping can be more useful than hiding rows with a one-way macro. In Excel, select the detail rows and use Data > Group. Excel adds outline controls that let a user collapse or expand the grouped section. VBA can also create a row outline with the Group method, for example ws.Rows("5:8").Group.
Grouping still operates on complete rows or columns, and a collapsed group is not a security boundary. Its advantage is usability: the worksheet communicates that details are available and gives the reader a convenient way to toggle them. Use direct hiding for a fixed display state; use grouping when people should control the level of detail themselves.
Common problems and safe macro use
- The wrong sheet changes: use a worksheet reference such as
ThisWorkbook.Worksheets("Report")rather than relying onActiveSheet. Confirm that the sheet name exists exactly as written. - Only part of the intended area disappears: check whether you targeted rows or columns. A cell range’s
EntireRoworEntireColumnproperty expands the target to full worksheet rows or columns. - Rows do not reappear: confirm that the unhide code targets the right sheet and includes the hidden rows. A filter may also be hiding records; clearing or changing the filter is different from setting
Hidden = False. - The macro cannot change the sheet: worksheet protection or workbook structure restrictions may interfere with changes. Review the protection setup and use the workbook’s intended password and permissions rather than trying to bypass them.
- The macro is unavailable: save macro-enabled workbooks as
.xlsmand allow macros only when the file and its source are trusted. Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros.
Before running a macro that changes worksheet visibility, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a backup is the dependable way to restore the original state if the code targets the wrong range. These examples use basic Excel VBA worksheet and range properties available in desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, though some Excel features and surrounding tools differ; Excel for the web does not run VBA.
Frequently Asked Questions
Can I hide a range without hiding its entire row?
No. Excel’s row and column hiding applies to complete rows or columns. To conceal only a few cells visually, redesign the report or move those values elsewhere; formatting is not secure hiding.
What is the VBA code to hide rows 2 through 10?
Use Rows("2:10").Hidden = True on the intended worksheet. In a reusable macro, qualify it with a worksheet variable, such as ws.Rows("2:10").Hidden = True.
How do I unhide all rows using VBA?
Use ws.Rows.Hidden = False after setting ws to the target worksheet. This can reveal every manually hidden row on that sheet, so use a specific row range if you want to preserve other hidden sections.
Does hiding a row remove its values from formulas?
No. Hiding changes what is displayed, not the stored values. Formulas can still refer to hidden cells, and hiding a row alone does not remove it from ordinary calculations.
Will this macro run in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Microsoft 365 on Windows or Mac to use VBA macros.
Is a hidden worksheet or row private?
No. Hidden content can generally be revealed by someone with access to the workbook, and worksheet protection is not a reliable way to keep sensitive data confidential. Share a workbook that does not contain information recipients should not see.
Related
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Split Function: How to Split Text into an ArrayIn VBA, use Split(text, delimiter) to break a string wherever a chosen delimiter appears; the result is an…
- Excel VBA Minimum: Find the Smallest ValueIn Excel VBA, use Application.WorksheetFunction.Min to return the smallest value from a range or a set of numbers;…
- Excel VBA: Run a Macro Automatically When a Workbook OpensYes. To run a VBA macro when an Excel workbook opens, put a Workbook_Open event procedure in that…
- Excel VBA: Copy a Worksheet and Rename ItTo copy a worksheet and rename the copy with VBA, use the worksheet’s Copy method, then set the…
- Trim Excel Macro: Clean Extra Spaces in Selected CellsIf the text is in A2, enter =TRIM(A2) in another cell to remove leading and trailing ordinary spaces…
- How to Set Up a Macro on a SteelSeries Mouse with GGTo put a macro on a SteelSeries mouse, install SteelSeries GG, open Engine, select your mouse, assign a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.