VBA code to list all hidden sheet in a workbook!!!

Excel allows user to hide worksheets which are not required or wanted it to be hidden. As a result, there will be numerous worksheets to be hidden in a single workbook. Sometime, we need to figure out which hidden sheets we want and which should be deleted from the workbook. In such scenario, list down all hidden sheets will be a quite manual task. Obviously, it can consume couple of hours if there are lot of workbooks contains hundred of worksheets in each workbook. So, this task can be automate with few line of excel macro.

  
Here is the VBA code to list all hidden sheet in the workbook: 

Option Explicit

Sub List_of_Hidden_Worksheet()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim strSht As String
Dim sht As Worksheet
Dim i As Integer

strSht = ""

i = 1

'For loop to check each worksheets for hidden property
    For Each sht In ActiveWorkbook.Worksheets
        
        If Not Worksheets(sht.Name).Visible Then
            
            'Pass names of hidden sheets into variable to show in message box. comment or delete this if not required
            strSht = strSht & i & ".  " & sht.Name & vbNewLine
            
            'Uncomment below line to get hidden Sheets list in Sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = sht.Name
            
            i = i + 1
        End If
    Next sht
    
MsgBox "List of hidden sheets as Below: " & vbNewLine & vbNewLine & strSht, vbInformation, "Hidden Worksheets"

End Sub