Unhide All Sheets And Cells Excel VBA Macro

In this post we see how to unhide all sheets and cells in Excel using VBA macros. The macro loops through each worksheet in the workbook to make any hidden or very hidden worksheet visible. At the same time and while in the loop, it also unhides all rows and columns, which subsequently unhides all the cells in that worksheet.

Macro/VBA code:

 

  Sub UnhideAllSheetsAndCells()

      Dim ws As Worksheet

 

      For Each ws In Worksheets

 

          'unhide worksheet

          ws.Visible = xlSheetVisible

   

          'unhide cells

          ws.Rows.Hidden = False

          ws.Columns.Hidden = False

 

      Next ws

  End Sub

 

 

Macro explained:

• We declare an object variable (ws) to store each worksheet in the workbook while looping
• Then we start a loop through each worksheet in the workbook (or to be more precise, within the Worksheets collection of the active workbook). We can target a different workbook by specifying the name or index, or the workbook where the macro is running referring to ThisWorkbook. In that case we would used For Each ws In ThisWorkbook.Worksheets
• Now we use the Visible property of the Worksheet object to make each sheet visible. That makes both hidden and very hidden worksheets visible. Note that we can only unhide “hidden” worksheets when done manually. Very hidden worksheets can only be unhidden from within the visual basic editor (specifically through the properties window) or with VBA code. The Visible property accepts the following three constants as part of XlSheetVisibility enumeration:

• xlSheetVisible 
• xlSheetHidden 
• xlSheetVeryHidden

• Next, we target all rows and columns in the worksheet, and set their Hidden property to false. In that way, the macro shows all rows and columns in the worksheet (regardless of their status: visible or hidden), and subsequently, it shows all the cells for each worksheet.

Note that while we can unhide protected worksheets, we cannot unhide rows or columns (or what is the same, cells) when a worksheet is protected. In that case, the macro will prompt an error.

Similarly, we cannot unhide (or hide) a worksheet when the workbook structure is protected. In that case, the macro will also prompt an error.

You may want to use error handling to anticipate that error. Check this section in the tutorial for beginners to learn more about error handling.

 

This is how we delete rows based on criteria with Excel VBA macros.

Other examples:

• How to Separate Words And Numbers with Excel VBA macros
  
• How to Delete Rows Based On Criteria with Excel VBA macros
  
• How to Upload Data To Google Sheets with Excel VBA macros