How to Unhide All Rows in Excel with VBA

by

If you’re in the habit of hiding rows in Excel, you may also sometimes, have a need to unhide all rows in a worksheet in Excel.

In this tutorial, I will show you can quickly unhide all the rows in the worksheet with a simple VBA macro code.

Unhide Rows in a Worksheet in Excel Using VBA

Below is the VBA code that will instantly unhide all the rows in the entire worksheet;

Sub UnhideAllRows()
Rows.EntireRow.Hidden = False
End Sub

In case you want to unhide sheets in a specific range only (let’s say unhide all hidden rows in the first 20 rows only), you can use the below code:

Sub UnhideSpecificROws()
For i = 1 To 20
    Rows(i).Hidden = False
Next i
End Sub

The above uses the FOR NEXT loop to go through each row in the first 20 rows and then makes the hidden property FALSE, which is equivalent to making all rows visible.

Also read: 100 Useful Excel VBA Macro Codes Examples

Unhide Rows in All the Worksheets in Excel Using VBA

In case you want to unhide rows in all the worksheets at one go, you can do that as well.

Below is the VBA code that will go through each worksheet in the active workbook and then unhide all the rows in that worksheet:

Sub UnhideSpecificROws()
For Each sh In Worksheets
    sh.Rows.EntireRow.Hidden = False
Next sh
End Sub

How to Use this VBA Code

To use this code, you need to add this to a module in the Visual Basic Editor in Excel, and then run this code from there.

Below are the steps to add this VBA code to unhide rows to a module in VB Editor:

  1. Right-click on any of the worksheet tabs
  2. Click on View Code. This will open the VB Editor windowRight click and then click on view tab
  3. In the VB Editor, there would be a Project Explorer at the left of the window. If you don’t see it, go to the View option in the menu and then click on Project Explorer. This will make it appear.
  4. In the Project Explorer, right-click on any of the objects (sheet names or ThisWorkbook object)
  5. Go to the Insert option and then click on Module. This will insert a new Module in the Project ExplorerInsert a new module
  6. Double Click on the Module to open its code window
  7. Copy and paste the above VBA code in that code windowCopy and paste the code in the module window
Tip: The keyboard shortcut to open the VB Editor window in ALT + F11 (hold the ALT key and then press the F11 key)

Once you have the code in the module, you need to run this code to unhide rows in Excel.

Below are the steps to run the macro code from the VB Editor:

  1. Place the cursor anywhere in the code
  2. Press the F5 key (or click on the green play button in the toolbar)Click the green button to run the macro
Note: Since now the workbook has a VBA macro in it, you need to save it in the macro-enabled format (.XLSM). If you save it as a regular .XLSX file, the macro will not be saved
Also read: Count Columns Using VBA in Excel

Adding the Macro to the Quick Access Toolbar

In case you have to unhide rows quite often, you can speed up the process by adding the VBA macro to the Quick Access toolbar. This way, when you have to unhide rows in Excel, you can simply click on the button and it will be done.

Below are the steps to add the macro to the Quick Access Toolbar:

  1. Click on the Customize Quick Access Toolbar (QAT) icon in the QATClick on the more commands icon in the quick access toolbar
  2. Click on More CommandsClick on More Commands
  3. In the Excel Options dialog box, click on Macros from the drop-down. You will notice that your macro is listed in thereClick on Macros option in the Poular command drop down
  4. Click on the macro that you want to add to the QAT
  5. Click on the ADD buttonSelect the macro and then click on Add
  6. Click OK

The above steps would add the macro to the Quick Access Toolbar.

Macro icon in the quick access toolbar

Now, when you have to unhide rows in Excel, you can simply click on this macro icon and it would be done.

Note that since this macro is available only in the workbook where you have added it, you will not be able to use it in other workbooks. In case you want to access this from all the workbooks, you need to save it in the Personal Macro Workbook or create and add this as an add-in.

So this is how you can quickly use VBA to unhide rows in Excel.

All the methods I have shown here can also be used to unhide columns in Excel. You just need to modify the code to replace rows with columns.

Hope you found this tutorial useful.

You may also like the following Excel tutorials:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment