excel - 使用 VBA 在 Excel 中查找隐藏工作表(和隐藏单元格)

Question

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

Answer 1

您可以循环浏览工作表、列和行，检查 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