Inspecting multiple Excel sheet at once

Question

I have been given a folder of about 200 Excel spreadsheets and need to open each one of them and "inspect" them for hidden rows or sheets. Is there a way to do this without having to open each file individually?

Answer 1

Open a new Excel file, Open VBA Editor there, "Insert" -> "Module", paste the following code there:

Sub search_for_hidden_sheet_or_cells()
Dim oFSO As Object: Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim f As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim c As Range: Set c = ThisWorkbook.Sheets(1).Cells(1, 1)
Dim r As Range

For Each f In oFSO.GetFolder("C:\Temp\test").Files
  If f.Name Like "*.xls*" Then
    Set wb = Application.Workbooks.Open(f)
    For Each ws In wb.Worksheets
      If ws.Visible = xlSheetHidden Then
        c = "Hidden sheet """ & ws.Name & """ in wb """ & wb.Name & """"
        Set c = c.Offset(1)
      ElseIf ws.Visible = xlSheetVeryHidden Then
        c = "Very hidden sheet """ & ws.Name & """ in wb """ & wb.Name & """"
        Set c = c.Offset(1)
      Else
        For Each r In ws.UsedRange.Rows
          If r.Hidden Then
            c = "Hidden row " & r.Row & " in sheet """ & ws.Name & """ in wb """ & wb.Name & """"
            Set c = c.Offset(1)
          End If
        Next
        For Each r In ws.UsedRange.Columns
          If r.Hidden Then
            c = "Hidden col " & Split(r.Address, "$")(3) & " in sheet """ & ws.Name & """ in wb """ & wb.Name & """"
            Set c = c.Offset(1)
          End If
        Next
      End If
    Next
    wb.Close
  End If
Next
End Sub

Instead of "C:\Temp\test" put in your folder address.

This code will find all sheets, columns and rows which are hidden in your Excel files. You will simply have to wait until the process finishes if there are many files. The result after my test looks like this: