How To Hide And Unhide Sheets In Excel With Vba

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.

Read more interesting post

Leave a comment