Hide or Unhide all Excel Sheets Without Looping

Question

I have a workbook with 179 worksheets. I would like to either hide all worksheets (leaving one unhidden per the rules of Excel), or unhide all worksheets.

Currently I have code that looks something like this (with the appropriate modifications depending on whether we are hiding/unhiding sheets):

For Each Sht in Wb.Worksheets
    Sht.Visible = xlSheetVisible
Next Sht

With 179 worksheets, this takes 4-5 seconds to run, and I would prefer it ran much faster.

I know that if I go into a workbook, manually select all sheets (except for one), then right-click and select "Hide", all of the sheets will be instantly hidden. I have been unable to reproduce this in VBA code.

As mentioned above, I need to somehow quickly hide all (but one) worksheets in a workbook and unhide all worksheets in a workbook without looping. Any help is much appreciated!

Answer 1

As mentioned, you can only Hide multiple sheets without a loop, like in this statement:

Worksheets(Array(1,2,3,4,5,6,7,8,9,10,11,...,200)).Visible = True

but Unhiding multiple sheets requires the loop

However, there is a much faster method available for this, using Custom Views (in View Tab)

---

The code bellow generates 2 views 1. "ShowAllWs", and 2. "HideAllWs"

Performance wise:

For 201 Worksheets

Loop HideAll - Time: 0.039 sec (initial setup - sets array, except one Ws in one operation)
Loop ShowAll - Time: 0.648 sec (initial setup - unhides all using a loop)

View ShowAll - Time: 0.023 sec (consecutive runs - no loop)
View HideAll - Time: 0.023 sec (consecutive runs - no loop)

---

Option Explicit

Public Sub SetWsVisibility(Optional ByVal vis As Boolean = False, _
                           Optional ByVal visibleWs As Long = 0)

    Static vSet As Boolean, hSet As Boolean, wsCount As Long, lastV As Long, i As Long

    With ThisWorkbook

        wsCount = .Worksheets.Count - 1

        'if visibleWs is 0 last ws is visible, or use any other valid sheet index
        visibleWs = IIf(visibleWs < 1 Or visibleWs > wsCount, wsCount + 1, visibleWs)

        If wsCount <> .Worksheets.Count - 1 Or visibleWs <> lastV Then
            vSet = False
            hSet = False
        Else
            If vSet And vis Then .CustomViews("ShowAllWs").Show:        Exit Sub
            If hSet And Not vis Then .CustomViews("HideAllWs").Show:    Exit Sub
        End If

        Application.ScreenUpdating = False
        If vis Then
            For i = 1 To wsCount + 1
                With .Worksheets(i)
                    If Not .Visible Then .Visible = vis
                End With
            Next
            .Worksheets(1).Activate
            .CustomViews.Add ViewName:="ShowAllWs"  'Save View (one-time operation)
            vSet = True
        Else
            If visibleWs <> lastV Then
                For i = 1 To wsCount + 1
                    With .Worksheets(i)
                        If Not .Visible Then .Visible = 1
                    End With
                Next
            End If

            Dim arr() As Variant, j As Long
            ReDim arr(1 To wsCount)
            j = 1
            For i = 1 To wsCount + 1
                If i <> visibleWs Then arr(j) = i Else j = j - 1
                j = j + 1
            Next
            .Worksheets(arr).Visible = vis
            .CustomViews.Add ViewName:="HideAllWs"  'Save View (one-time operation)
            hSet = True
            lastV = visibleWs
        End If
        Application.ScreenUpdating = True
    End With
End Sub

---

To call it use this:

Public Sub UpdateWsVisibility()

    SetWsVisibility 0, 5    'or 0 to hide them (or True / False respectively)

End Sub

Answer 2

Assuming your workbook isn't protected, here's a sub that will do what's possible without systematically looping. Chart-type sheets are supported.

I've just learned that referring to sheets through an array only works if they are all visible, hence a loop is necessary when making sheets visible.

'Hides all sheets in the workbook containing pExceptThisSheet, except pExceptThisSheet.
'Note: pExceptThisSheet is declared as an Object so as to support both the Worksheet and Chart types.
Public Sub HideAllSheetsBut(ByVal pExceptThisSheet As Object)
    On Error GoTo errHandler

    Dim vntAllSheetsBut() As Variant
    Dim oSht As Object
    Dim lIndex As Long
    Dim bScreenUpdating As Boolean

    bScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    'Show all sheets.
    'Note: for some reason, an array can't be used here; must loop.
    For Each oSht In pExceptThisSheet.Parent.Sheets
        If oSht.Visible <> xlSheetVisible Then
            oSht.Visible = xlSheetVisible
        End If
    Next

    If Not pExceptThisSheet Is Nothing Then
        If pExceptThisSheet.Parent.Sheets.Count > 1 Then        
            'Hide all sheets but the specified one.        
            ReDim vntAllSheetsBut(0 To pExceptThisSheet.Parent.Sheets.Count - 2) As Variant

            lIndex = 0
            For Each oSht In pExceptThisSheet.Parent.Sheets
                If Not oSht Is pExceptThisSheet Then
                    vntAllSheetsBut(lIndex) = oSht.Name
                    lIndex = lIndex + 1
                End If
            Next
            'Note: for some reason, this only works for hiding, and if all sheets in vntAllSheetsBut are visible.
            'A possible explanation would be that, behind the scene, Excel attempts to select the sheets, and fails when it encounters hidden ones.
            pExceptThisSheet.Parent.Sheets(vntAllSheetsBut).Visible = xlSheetHidden
        End If
    End If

Cleanup:
    On Error Resume Next
    Set oSht = Nothing
    Application.ScreenUpdating = bScreenUpdating
    Exit Sub

errHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume Cleanup
End Sub

You can call the sub like this:

HideAllSheetsBut Sheet1

or

HideAllSheetsBut Application.Workbooks("MyWorkbook.xlsx").Worksheets("MyWorksheet")

to hide all but one worksheet, and like this:

HideAllSheetsBut Nothing

to show all sheets.

EDIT As mentioned in paul bica's answer, a custom View is a great way to quickly flip the visibility of several worksheets. In my answer, it would be used to show all worksheets without having to loop over them.

Answer 3

Maybe try this:

Application.ScreenUpdating = False

For Each Sht in Wb.Worksheets
    If Sht.Visible = xlSheetHidden Then
        Sht.Visible = xlSheetVisible
    End If
Next Sht

Application.ScreenUpdating = True

Answer 4

As suggested you could turn off screen updating. Add the IF statement below to omit the sheet you want (it's called 'Main' in mine, change this to what you want)

Sub HideSheets()

    Dim sht As Worksheet

    Application.ScreenUpdating = False

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> "Main" Then sht.Visible = False
    Next sht

    Application.ScreenUpdating = True

End Sub

Caleeco

Answer 5

try this ... from a recorded macro

Option Explicit

Sub HideSheets()

    Worksheets.Select
    Sheets("Main").Activate
    ActiveWindow.SelectedSheets.Visible = False

End Sub