To hide a specific block of worksheet rows in Excel for Microsoft 365 on Windows, use VBA to set those rows’ Hidden property to True. For example, Rows("5:10").Hidden = True hides rows 5 through 10 on the active worksheet; the full rows are hidden, not just the cells in a selected column.
Hide a fixed range of rows with VBA
This macro hides rows 5 to 10 on a worksheet named Data. Change the sheet name and row numbers to match the workbook. The macro belongs in a standard VBA module.
Sub HideRowsInSpecificRange()
Dim ws As Worksheet
Dim targetRows As Range
' Identify the worksheet and the complete row range.
Set ws = ThisWorkbook.Worksheets("Data")
Set targetRows = ws.Rows("5:10")
' Hide every worksheet row in the range.
targetRows.Hidden = True
End Sub
The range text “5:10” refers to entire worksheet rows, so the setting applies across all columns, including columns not currently visible on screen. If the target is instead expressed as a cell range, such as B5:D10, use its EntireRow property to hide the rows that intersect it:
Sub HideRowsIntersectingCellRange()
Dim ws As Worksheet
Dim targetCells As Range
' This cell range intersects rows 5 through 10.
Set ws = ThisWorkbook.Worksheets("Data")
Set targetCells = ws.Range("B5:D10")
targetCells.EntireRow.Hidden = True
End Sub
In both examples, the worksheet name must match the tab name exactly. A misspelled name or a sheet that does not exist in the workbook causes a run-time error rather than hiding rows on another sheet.
Put the macro in a module and run it
Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if a macro hides or changes the wrong range. If the workbook must retain its macro, save it as an Excel Macro-Enabled Workbook (.xlsm), not as a standard .xlsx file, which does not store VBA code.
- Open the workbook in the Excel desktop app. VBA macros cannot be created or run in Excel for the web.
- If the Developer tab is missing, open File > Options > Customize Ribbon, select Developer, and choose OK.
- Choose Developer > Visual Basic, then in the Visual Basic Editor choose Insert > Module.
- Paste the macro into the module. Update the worksheet name and row range as needed.
- Return to Excel, choose Developer > Macros, select the macro, and choose Run. On Windows, Alt+F8 opens the Macro dialog, and Alt+F11 opens the Visual Basic Editor.
Excel for Mac also supports VBA macros and the same basic Worksheet, Range, and Hidden objects used here. The Developer tab can be enabled in Excel > Preferences (or Settings, depending on the release) > Ribbon & Toolbar. Keyboard shortcuts can differ by Mac keyboard and system settings, so use Developer > Visual Basic and Developer > Macros if a Windows shortcut does not work. Excel for the web can display a workbook containing macros, but it cannot run or edit VBA.
Choose the right way to describe the range
Use a row reference when the target is known in advance: ws.Rows("5:10") is straightforward and clearly means complete rows. Use a cell reference with EntireRow when the relevant range is easier to identify by columns and cells, such as ws.Range("B5:D10").EntireRow. Either approach hides every row touched by the reference.
For a range based on a cell address, the address must be valid on the named worksheet. If a user selects a different sheet while the macro runs, a macro that uses ThisWorkbook.Worksheets("Data") still targets Data. This explicit reference is generally safer than relying on ActiveSheet, because the active sheet can change as users navigate the workbook.
- Fixed row block: use
ws.Rows("5:10").Hidden = True. - Rows crossing a cell block: use
ws.Range("B5:D10").EntireRow.Hidden = True. - One row: use
ws.Rows(5).Hidden = True. - Rows chosen by a rule: loop through a bounded range and hide a row when its value meets the rule.
A frequent misconception is that hiding B5:D10 hides only those cells. Excel does not hide part of a worksheet row: setting EntireRow.Hidden hides the complete row across the sheet. If the goal is to conceal only some values while leaving the row visible, use a different approach, such as formatting or controlling which data is displayed.
Hide rows when cells meet a condition
VBA can also examine a specific range and hide only the rows whose cells match a condition. This example checks B2:B100 on Data and hides a row when column B contains the word Closed. It assumes row 1 is a header and that the status is in column B. The comparison is case-insensitive because VBA’s default text comparison is ordinarily case-insensitive in this context.
Sub HideClosedRows()
Dim ws As Worksheet
Dim statusCell As Range
' Check only the data rows, leaving the header untouched.
Set ws = ThisWorkbook.Worksheets("Data")
For Each statusCell In ws.Range("B2:B100")
If StrComp(CStr(statusCell.Value), "Closed", vbTextCompare) = 0 Then
statusCell.EntireRow.Hidden = True
End If
Next statusCell
End Sub
The bound B2:B100 is deliberate. A loop through a large worksheet column can take longer than necessary, and a fixed range makes the macro’s scope easier to inspect. If the data grows, update the final row or determine it dynamically with a suitable last-row method. Also note that this macro only hides matching rows; it does not unhide rows that no longer match. A row changed from Closed to Open can therefore remain hidden until another procedure unhides it.
For values that may contain spaces, blank cells, or formulas returning an empty string, decide explicitly what should count as a match. For example, comparing a trimmed value can handle leading and trailing spaces, but it should be added only if those spaces are possible in the source data. Avoid changing the comparison rule without checking whether values such as “Closed – pending” should also qualify.
Unhide rows and restore the worksheet
To reverse the fixed-range example, set the same rows’ Hidden property to False. This can be a separate macro, or a line in a procedure that restores the view after a task.
Sub UnhideRowsInSpecificRange()
Dim ws As Worksheet
' Restore rows 5 through 10 on the named worksheet.
Set ws = ThisWorkbook.Worksheets("Data")
ws.Rows("5:10").Hidden = False
End Sub
To show all rows on a worksheet, use ws.Rows.Hidden = False. That is broader than restoring a specific block: it also reveals rows that may have been intentionally hidden for another reason. Choose the narrowest range that matches the intended change. If a row stays hidden after setting the property to False, check whether the worksheet is protected, whether another macro hides it again, or whether the row is hidden by a filter.
For a manual check, select the row headings above and below a hidden block, right-click, and choose Unhide. You can also select the whole worksheet using the button at the intersection of the row and column headings, then use Home > Format > Hide & Unhide > Unhide Rows. Unhiding the whole sheet can reveal deliberately concealed rows, so use it carefully.
VBA hiding versus filtering
Directly setting Hidden = True is useful when the target rows are fixed or when a macro applies a custom rule. It changes the row’s hidden state, and Excel does not record why the row was hidden. If several processes use hidden rows, one process can unintentionally reveal rows another process meant to keep hidden.
AutoFilter is often a better choice for a table of records that users need to sort, search, or show by criteria. A filter hides records that do not meet its criteria and provides visible controls for changing those criteria. It is more transparent for routine data exploration, whereas VBA is helpful for a repeatable action, a custom condition, or a controlled worksheet view. Avoid applying a filter and a separate row-hiding macro to the same data unless the interaction is intentional; the visible result can be confusing to maintain.
Manual hiding is simplest for a one-time change. A macro is worthwhile when the same operation must be repeated reliably, when the rule is more specific than a convenient filter, or when it is part of a larger automated process. VBA does not make a workbook’s contents secure: hidden data can be unhidden, so do not use row hiding to protect confidential information.
Common errors and safe-use considerations
Most problems come from an incorrect target or from assumptions about what hidden means. Check these points if the macro appears not to work:
- Wrong worksheet: confirm that the sheet tab name matches the string in
Worksheets. A workbook reference and worksheet reference should be explicit. - Wrong row numbers: remember that row 1 is the first worksheet row, and a header row is included if the chosen range includes it.
- Protected sheet: sheet protection can prevent changes to row formatting or visibility. Review protection settings and the workbook’s intended access controls before modifying protection in code.
- Filter still active: a filter can hide rows independently of the
Hiddenproperty. Clear or inspect the filter when diagnosing which rows are visible. - Macro did not run: check that the file is macro-enabled and that Excel’s security settings allow the macro. Do not enable macros in an unfamiliar file just to make it work.
- No visible change: the target rows may already be hidden, may be outside the current view, or may be hidden again by another procedure.
Use macros only from sources you trust, and keep a backup before running code that changes a workbook. A hidden row is still part of the workbook and may be exposed by unhiding it, copying data, or using other tools. Hiding is a display and organization feature, not encryption or access control.
Frequently Asked Questions
Can VBA hide rows in a specific range without hiding the whole sheet?
Yes. Set Hidden = True on a limited row range, such as ws.Rows("5:10"). The specified rows are hidden; the rest of the worksheet remains visible.
Can I hide rows based on a value in one column?
Yes. Loop through the relevant data cells, test each value, and set EntireRow.Hidden = True for matches. Use a bounded range and leave header rows out of the loop unless they are intentionally part of the rule.
How do I unhide rows with VBA?
Set the same range’s Hidden property to False. To reveal every row, use ws.Rows.Hidden = False, but remember that this may reveal rows hidden for unrelated reasons.
Can Excel for the web run this VBA macro?
No. Excel for the web can open and edit many workbook contents, but VBA macros must be run from the Excel desktop app. Excel for Mac also supports VBA for this basic row-hiding task.
Can I undo a row-hiding macro with Ctrl+Z?
No. Excel’s undo history does not undo VBA changes. Save a copy before running the macro, or run a separate macro that unhides the exact rows if you need to restore them.
Does hiding rows protect their data?
No. Hidden rows remain in the workbook and can be unhidden. Use appropriate workbook access controls and avoid treating row hiding as a security measure.
Related
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Split Data Cleanly and Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Delete Rows in a Range: Safe Methods and ExamplesTo delete entire worksheet rows that intersect a range, use the range’s EntireRow property and call Delete, for…
- VBA Delete Dictionary Items, Keys, or the Whole ObjectIn VBA, remove one entry from a Scripting.Dictionary with dict.Remove key, clear every entry but keep the dictionary…
- Excel VBA Copy: How to Copy Cells, Sheets, and FilesTo copy cells with Excel VBA, use Range.Copy with a destination, such as Worksheets("Sales").Range("A1:D20").Copy Destination:=Worksheets("Summary").Range("A1"). This copies the…
- Excel VBA Macro to Copy Data Between SheetsTo copy data between worksheets with an Excel VBA macro, assign the source range’s values to a destination…
- Excel UserForm Checkbox: Add, Code, and TroubleshootAn Excel UserForm checkbox is a clickable VBA control that lets someone choose a yes-or-no option in a…
- Excel VBA: How to End a LoopTo end a VBA loop early, use Exit For inside a For loop or Exit Do inside a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.