使用 VBA 在 excel 中查找隐藏的工作表（和隐藏的单元格）

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

提问by smfoote

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

有没有办法使用 VBA 确定 Excel 工作簿是否具有隐藏的工作表和/或隐藏的单元格？谢谢！

回答by Banjoe

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.

您可以遍历工作表、列和行，检查 worksheet.visible 和 range.hidden 属性。下面是一些将任何隐藏元素输出到直接窗口的快速而肮脏的代码。

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

回答by ProdOps

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.

我将下面的过程保存在我的 Personal.xls 文件中，并在快速访问工具栏上有一个按钮来运行它。它在弹出对话框中显示所有隐藏的工作表和非常隐藏的工作表，还使您能够取消隐藏单个工作表或所有隐藏、所有非常隐藏或两者。

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.

这不会显示隐藏的单元格/行/列，但对于查找和取消隐藏工作表非常有用。我使用上面提到的 Dave 的 Mappit Addin 进行更详细的分析。

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

回答by brettdj

Another option is my (free) Mappit! addin available herewhich highlights

另一种选择是我的（免费）Mappit！插件可用在这里突出显示

1. hidden areas on each sheet tested (see pink shaded area below),
2. and also produces an interlinked sheet summary which lists how worksheets are connected with discrete formulae (regardless of whether they are visible, hidden or very hidden)

1. 每张测试纸上的隐藏区域（见下面的粉红色阴影区域），
2. 并生成一个相互链接的工作表摘要，其中列出了工作表如何与离散公式连接（无论它们是可见的、隐藏的还是非常隐藏的）

Your question has prompted me to look at updating the sheet link output to colour highlight which sheets are hidden, or very hidden.

您的问题促使我考虑更新工作表链接输出以突出显示哪些工作表隐藏或非常隐藏。

[Update: MappitV1.11 updated as below to provide info on sheet visibility. Now further updated to MappitV1.11a as empty sheets that were hidden were not being flagged on the summary sheet]

[更新：MappitV1.11 更新如下，以提供有关工作表可见性的信息。现在进一步更新到 MappitV1.11a，因为隐藏的空表没有在汇总表上标记]

回答by aevanko

Here is a method, very similar to Banjoe's, that will return the count of how many hidden sheets, columns, and rows there are (assuming you don't need the info on which rows and just want a report).

这是一种与 Banjoe 非常相似的方法，它将返回隐藏的工作表、列和行的数量（假设您不需要有关哪些行的信息而只需要报告）。

• Note that using the 'UsedRange' for rows/columns means that the count will not include rows/columns that do not contain any data (but the macro will be faster as a result).

• 请注意，对行/列使用“UsedRange”意味着计数将不包括不包含任何数据的行/列（但宏因此会更快）。

Here is the code:

这是代码：

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

Please note that you can also use the "inspect document" feature in Excel to see if a document has hidden sheets/rows/columns.

请注意，您还可以使用 Excel 中的“检查文档”功能来查看文档是否有隐藏的工作表/行/列。