Finding hidden sheets (and hidden cells) in excel with VBA
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

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

Sub HiddenReport()

Application.ScreenUpdating = False
Dim wks As Worksheet
Dim rng As Range
Dim sCount As Long, rCount As Long, cCount As Long

For Each wks In ThisWorkbook.Worksheets
    If wks.Visible = xlSheetHidden Then sCount = sCount + 1
    If wks.Visible = xlSheetVeryHidden Then sCount = sCount + 1

    For Each rng In wks.Rows ' or wks.UsedRange.Rows
        If rng.Hidden = True Then rCount = rCount + 1
    Next

    For Each rng In wks.Columns ' or wks.UsedRange.Columns
        If rng.Hidden = True Then cCount = cCount + 1
    Next
Next

Application.ScreenUpdating = True
MsgBox sCount & " hidden sheets found." & vbLf & _
       rCount & " hidden rows found." & vbLf & _
       cCount & " hidden columns found."

End Sub