How to Hide and Unhide Sheets in Excel with VBA
Using VBA (Visual Basic for Applications), you can program Excel to automate various tasks. One such task is hiding and unhiding sheets within an Excel workbook.
Hide a Sheet
To hide a sheet in Excel using VBA, you can use the .Visible
property of the Worksheet
object. Here’s an example:
Sub HideSheet()
Sheets("Sheet1").Visible = xlSheetHidden
End Sub
In this example, the sheet named “Sheet1” will be hidden when this macro is executed.
Unhide a Sheet
To unhide a sheet in Excel using VBA, you can again use the .Visible
property, but this time set it to xlSheetVisible
. Here’s an example:
Sub UnhideSheet()
Sheets("Sheet1").Visible = xlSheetVisible
End Sub
Executing this macro will make the previously hidden “Sheet1” visible again.
Example: Hide Multiple Sheets
You can also hide multiple sheets at once using VBA. Here’s an example that hides three sheets:
Sub HideMultipleSheets()
Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Visible = xlSheetHidden
End Sub
In this example, sheets “Sheet2”, “Sheet3”, and “Sheet4” will be hidden when this macro is executed.
Example: Unhide All Sheets
If you want to unhide all sheets within an Excel workbook, you can use the following code:
Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
This example loops through all the worksheets in the workbook and sets their visibility to be visible.
Remember to replace “Sheet1”, “Sheet2”, “Sheet3”, etc., with the actual names of the sheets you want to hide or unhide, and adjust the code according to your specific requirements.