Finding hidden sheets (and hidden cells) in excel with VBA

Question

Is there a way to determine if an Excel workbook has hidden sheets and/or hidden cells using VBA? Thanks!

Answer 1

You can loop through the worksheets, columns, and rows checking the worksheet.visible and range.hidden properties. Below is some quick and dirty code that will output any hidden elements to the immediate window.

Sub FindHidden()
    Dim wks As Worksheet
    Dim rng As Range

    For Each wks In ThisWorkbook.Worksheets
        If wks.Visible = xlSheetHidden Then
            Debug.Print "Worksheet: " & wks.Name & " is hidden."
        ElseIf wks.Visible = xlSheetVeryHidden Then
            Debug.Print "Worksheet: " & wks.Name & " is very hidden."
        End If

        For Each rng In wks.UsedRange.Rows
            If rng.Hidden = True Then
                Debug.Print "Worksheet: " & wks.Name & " Hidden Row: " & rng.Row
            End If
        Next rng

        For Each rng In wks.UsedRange.Columns
            If rng.Hidden = True Then
                Debug.Print "Worksheet: " & wks.Name & " Hidden Column: " & Left(Replace(rng.Address, "$", ""), 1)
            End If
        Next rng
    Next wks
End Sub

Answer 2

I keep the procedure below in my Personal.xls file and have a button on the Quick Access Toolbar to run it. It displays all hidden sheets and very hidden sheets in a pop up dialog that also gives you the ability to unhide a single sheet or all Hidden, all veryHidden or both.

This does not show the hidden cells/rows/columns but has been very useful for finding and unhiding sheets. I use Dave's Mappit Addin noted above for the more detailed analysis.

Code is below:

    Sub UnHideStuff()

'----------------------------------------------------------------------------
' UnHideStuff Macro
' Written by ProdOps
' 13-Feb-2010
'
' Provides an input dialog box that displays the names of all Hidden and all
' VeryHidden worksheets in the workbook and allows the user to enter the
' name of the worksheet they want to unhide.
' *   will unhide all Veryhidden sheets
' **  will unhide all Hidden sheets.
' *** will unhide all worksheets in the workbook
'
'----------------------------------------------------------------------------

 Dim Message As String
 Dim Title As String
 Dim Default As String
 Dim myValue As String
 Dim myList As String
 Dim Sheetnum As Long

'Build a list of VeryHidden Sheets
 myList = "'INVISIBLE WORKSHEET NAMES(*)':"
 For Sheetnum = 1 To Sheets.Count
   If Sheets(Sheetnum).Visible = 2 Then
     myList = myList & vbCrLf & "   " & Sheets(Sheetnum).Name
   End If
 Next Sheetnum
 If myList = "'INVISIBLE WORKSHEET NAMES(*)':" Then
   myList = myList & vbCrLf & "   No Invisible Sheets in This Workbook"
 End If

 'Build a list of Hidden Sheets
 myList = myList & vbCrLf & vbCrLf & "'HIDDEN WORKSHEET NAMES(**)':"
 For Sheetnum = 1 To Sheets.Count
   If Sheets(Sheetnum).Visible = 0 Then
     myList = myList & vbCrLf & "   " & Sheets(Sheetnum).Name
   End If
 Next Sheetnum
 If Right(myList, 11) = "NAMES(**)':" Then
   myList = myList & vbCrLf & "   No Hidden Sheets in This Workbook"
 End If

 'Build the Textbox Message & Title
 Message = "Enter the 'Name' of the WorkSheet to Unhide" & vbCrLf
 Message = Message & "Or * - All Invisible, ** - All Hidden, *** - All" & vbCrLf & vbCrLf
 Message = Message & myList
 Title = "Unhide Hidden Worksheets"
 Default = ""

 'Display the Message Box and retrive the user's input
 myValue = InputBox(Message, Title, Default)

 'Test the value entered by the user
 If myValue = "" Then Exit Sub  'User pressed CANCEL

 If myValue = "*" Then   'User wants all the VeryHidden sheets displayed
   For Sheetnum = 1 To Sheets.Count
    If Sheets(Sheetnum).Visible = 2 Then Sheets(Sheetnum).Visible = True
   Next Sheetnum
   GoTo NormalExit
 End If

 If myValue = "**" Then   'User wants all the Normal Hidden sheets displayed
   For Sheetnum = 1 To Sheets.Count
    If Sheets(Sheetnum).Visible = 0 Then Sheets(Sheetnum).Visible = True
   Next Sheetnum
   GoTo NormalExit
 End If

 If myValue = "***" Then   'User wants all worksheets displayed
   For Sheetnum = 1 To Sheets.Count
    Sheets(Sheetnum).Visible = True
   Next Sheetnum
   GoTo NormalExit
 End If

 On Error GoTo ErrorTrap
 Sheets(myValue).Visible = xlSheetVisible
 Sheets(myValue).Select
 Range("A1").Select

NormalExit:
 Exit Sub

ErrorTrap:
 If Err = 9 Then
   MsgBox "Either the Worksheet Does Not Exist or " & vbCrLf & "the Worksheet Name was Misspelled", vbCritical, "Worksheet Not Found"
   Err.Clear
   Call UnHideStuff
 End If

End Sub