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.
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:
- Right-click on any of the worksheet tabs
- Click on View Code. This will open the VB Editor window
- 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.
- In the Project Explorer, right-click on any of the objects (sheet names or ThisWorkbook object)
- Go to the Insert option and then click on Module. This will insert a new Module in the Project Explorer
- Double Click on the Module to open its code window
- Copy and paste the above VBA code in that code window
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:
- Place the cursor anywhere in the code
- Press the F5 key (or click on the green play button in the toolbar)
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:
- Click on the Customize Quick Access Toolbar (QAT) icon in the QAT
- Click on More Commands
- In the Excel Options dialog box, click on Macros from the drop-down. You will notice that your macro is listed in there
- Click on the macro that you want to add to the QAT
- Click on the ADD button
- Click OK
The above steps would add the macro to 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:
- How to Clear Contents in Excel without Deleting Formulas
- How to Select Every Other Cell in Excel (Or Every Nth Cell)
- How to Delete a Sheet in Excel Using VBA
- How to Copy Multiple Sheets to a New Workbook in Excel
- How to Select Alternate Columns in Excel (or every Nth Column)
- How to Filter as You Type in Excel (With and Without VBA)
- How to Hide Columns Based on Cell Value in Excel