Software

Use VBA to find hidden Excel sheets

Excel's ThisWorkbook object lets you expose and change a worksheet's visibility.

If you have hidden sheets in Excel, it might be helpful to programmatically manipulate those sheets. You might want to hide or unhide a sheet or just run an audit to make sure no one has changed your settings. The following sub procedure prints the visibility status of each sheet in the workbook: Private Sub ReportVisibility()   'List very hidden sheets in Immediate window   Dim ws As Worksheet   For Each ws In ThisWorkbook.Worksheets     If ws.Visible = xlSheetVeryHidden Then       Debug.Print ws.Name & " is very hidden"     ElseIf ws.Visible = xlSheetHidden Then       Debug.Print ws.Name & " is hidden"     End If Next End Sub

  

Of course, you can modify this procedure to do most anything you want with the hidden sheets. Just add the appropriate code to the If statement. This particular sub doesn't explicitly identify visible sheets. Anything not listed should be visible. If you want to see a comprehensive list of all sheets, add a condition for visible—xlSheetVisible.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox