Unhiding Multiple Worksheets

When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets can be a different story, however.

If you are using Excel 2021 or the version of Excel in Microsoft 365, you can unhide multiple worksheets at once. All you need to do is right-click on a worksheet tab and choose the Unhide option from the resulting Context menu. This displays the Unhide dialog box, which lists all the hidden worksheets. At this point you can create a selection set of the worksheets you want to unhide—hold down the Ctrl key as you click on each worksheet you want to unhide, or you can click on the first worksheet and then hold the Shift key down as you click on the last. When you've selected all the worksheets you want to unhide, click the OK button and you are good to go.

This capability is not available in older versions of Excel, however. In those, you can only unhide one worksheet at a time. If you have many worksheets you want to unhide, this can be very tedious.

The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook:

Sub UnhideAllSheets()
    Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
        wsSheet.Visible = xlSheetVisible
    Next wsSheet
End Sub

If you would rather not unhide all the worksheets at once, you can cause the macro to ask about each hidden worksheet and then unhide each that you agree to unhide. The following macro will handle this task:

Sub UnhideSomeSheets()
    Dim sSheetName As String
    Dim sMessage As String
    Dim Msgres As VbMsgBoxResult

    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible = xlSheetHidden Then
            sSheetName = wsSheet.Name
            sMessage = "Unhide the following sheet?" _
              & vbNewLine & sSheetName
            Msgres = MsgBox(sMessage, vbYesNo)
            If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible
        End If
    Next wsSheet
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9636) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Unhiding Multiple Worksheets.