Блог

Есть ли способ определить, есть ли в книге Excel скрытые листы и / или скрытые ячейки с помощью VBA? Спасибо!

Вы можете перебирать листы, столбцы и строки, проверяя свойства 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: " amp; wks.Name amp; " is hidden."
        ElseIf wks.Visible = xlSheetVeryHidden Then
            Debug.Print "Worksheet: " amp; wks.Name amp; " is very hidden."
        End If

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

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

Я сохраняю приведенную ниже процедуру в своем Personal.xls файл и иметь кнопку на панели быстрого доступа, чтобы запустить его. Он отображает все скрытые листы и очень скрытые листы во всплывающем диалоговом окне, которое также дает вам возможность отобразить один лист или все скрытые, все очень скрытые или оба.

Это не показывает скрытые ячейки / строки / столбцы, но было очень полезно для поиска и отображения листов. Я использую надстройку Mappit от Дейва, указанную выше, для более подробного анализа.

Код приведен ниже:

     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 amp; vbCrLf amp; "   " amp; Sheets(Sheetnum).Name
   End If
 Next Sheetnum
 If myList = "'INVISIBLE WORKSHEET NAMES(*)':" Then
   myList = myList amp; vbCrLf amp; "   No Invisible Sheets in This Workbook"
 End If

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

 'Build the Textbox Message amp; Title
 Message = "Enter the 'Name' of the WorkSheet to Unhide" amp; vbCrLf
 Message = Message amp; "Or * - All Invisible, ** - All Hidden, *** - All" amp; vbCrLf amp; vbCrLf
 Message = Message amp; 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 " amp; vbCrLf amp; "the Worksheet Name was Misspelled", vbCritical, "Worksheet Not Found"
   Err.Clear
   Call UnHideStuff
 End If

End Sub
  

Вот метод, очень похожий на метод Банджо, который вернет количество скрытых листов, столбцов и строк (при условии, что вам не нужна информация о том, какие строки и просто нужен отчет).

• Обратите внимание, что использование ‘UsedRange’ для строк / столбцов означает, что счетчик не будет включать строки / столбцы, которые не содержат никаких данных (но в результате макрос будет выполняться быстрее).

Вот код:

 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 amp; " hidden sheets found." amp; vbLf amp; _
       rCount amp; " hidden rows found." amp; vbLf amp; _
       cCount amp; " hidden columns found."

End Sub
  

Обратите внимание, что вы также можете использовать функцию «проверить документ» в Excel, чтобы увидеть, есть ли в документе скрытые листы / строки / столбцы.

Другой вариант — мой (бесплатный) Mappit! здесь доступно дополнение, которое выделяет

1. проверенные скрытые области на каждом проверенном листе (см. Розовую заштрихованную область ниже),
2. а также создает взаимосвязанную сводку листов, в которой перечисляется, как листы связаны с отдельными формулами (независимо от того, являются ли они видимыми, скрытыми или очень скрытыми)

Ваш вопрос побудил меня взглянуть на обновление вывода ссылки на лист, чтобы выделить цветом, какие листы скрыты или очень скрыты.

[Обновление: MappitV1.11 обновлено, как показано ниже, для предоставления информации о видимости листа. Теперь дополнительно обновлено до MappitV1.11a, поскольку пустые листы, которые были скрыты, не помечались на сводном листе]