While working, we need to unhide rows in Excel frequently. To know whether the rows are hidden or not carefully check the row numbers if some numbers are missing that means rows are hidden. It is easy to do so, and you can unhide rows in Excel in different shortcut ways. In this article, I am going to discuss three shortcuts on how to unhide rows in Excel in various cases. These shortcuts will certainly make your tasks interesting.
This is the dataset for this article. We have a list of students along with their hometowns and departments. You will observe that the 5th, 7th, 8th, 10th, 12th, and 15th rows are hidden here. We will unhide these rows using several methods.
How to Unhide Rows in Excel: 3 Quick Shortcuts
1. Unhide a Row by Double Click
You can very easily unhide a row with double clicks in Excel. In the datasheet, the 5th row is hidden. If you want to unhide the 5th row,
Place your mouse point in the middle of the 4th and 6th row. A double-sided arrow will appear.
Then simply double click the mouse. Excel will unhide the 5th row.
You can easily unhide all other rows in this way.
Read More: Hide Rows and Columns in Excel
2. Unhide Rows in Excel in Shortcut (Using CTRL + SHIFT + 9)
Now I am going to discuss how to unhide rows in Excel using Keyboard shortcuts. You must use CTRL + SHIFT + 9. Let’s discuss them one by one.
2.1. Unhide a Row using CTRL + SHIFT + 9
To unhide a row,
First, select the rows adjacent to the row you want to unhide. For instance, I am going to unhide the 5th row. So, I have to select the 4th and 6th row.
Then press CTRL + SHIFT + 9.
Excel will unhide the 5th row, as it was in between the selected 4th and 6th row.
2.2. Unhide Several Adjacent Rows using CTRL + SHIFT + 9
You can also unhide several adjacent rows using CTRL + SHIFT + 9.
To unhide adjacent rows (in our case it is the 7th and 8th row),
First, select the 6th and 9th rows.
Then press CTRL + SHIFT + 9.
The 7th and 8th rows will appear.
2.3. Unhide Several Non-Adjacent Rows using CTRL + SHIFT + 9
You can also unhide several non-adjacent rows following the same method. For instance, to unhide the multiple rows (in our case it is the 10th, 12th, and 15th row),
Select a range of rows where the hidden rows exist. Put another way, select from 9th row to 16th row.
Then press CTRL + SHIFT + 9.
The 10th, 12th, and 15th rows will appear.
Similar Readings
- How to Hide Rows Based on Cell Value in Excel
- Excel Hide Rows Based on Cell Value with Conditional Formatting
- How to Hide the Same Rows Across Multiple Excel Worksheets
3. Unhide Rows in Excel in Shortcut using VBA
We can unhide rows using VBA too. I am going to describe it in this section.
3.1. Shortcut to Unhide a Row in Excel using VBA
Here, I am going to explain how to unhide a row using VBA. To unhide a row, (the 5th row in this case)
Go to Developer tab >> select Visual Basic
Then go to Insert tab >> select Module
Then write down the following code.
Sub Unhide_A_Row()
Worksheets("Unhide a Row VBA").Range("5:5").EntireRow.Hidden = False
End Sub
Here, I have created a Sub Procedure Unhide_A_Row and mentioned the worksheet that I am going to work with. I have used the Range.Hidden property and set it False as I wanted to unhide the entire row. Range (“5:5”) indicates that the range starts and ends in the 5th row.
Then run the program.
Excel will unhide the 5th row in the “Unhide a Row VBA” datasheet.
Read More: Unhide All Rows Not Working in Excel
3.2. Shortcut to Unhide Rows in Excel using VBA (Adjacent)
We can also unhide several adjacent rows in Excel using VBA. In our dataset, 7th, and 8th, two adjacent rows are hidden. To unhide these rows I will use VBA.
To open the VBA editor and to insert a new module follow the steps explained in section 3.1.
Then write down the following code,
Sub Unhide_Adjacent_Rows()
Worksheets("Unhide Adjacent Rows VBA").Range("7:8").EntireRow.Hidden = False
End Sub
Here, I have created a Sub Procedure Unhide_Adjacent_Rows and mentioned the worksheet that I am going to work with. I have used the Range.Hidden property and set it False as I wanted to unhide the entire row. Range from (“7:8”) indicates that the range starts with the 7th row and ends at the 8th row.
Now run the program. Excel will unhide the 7th and 8th rows.
3.3. Shortcut to Unhide Rows in Excel using VBA (Non-Adjacent)
Now, let’s see how we can unhide several non-adjacent rows in Excel. The 10th, 12th, and 15th rows are non-adjacent and hidden.
To open the VBA editor and to insert a new module follow the steps explained in section 3.1.
After inserting a new module, write down the following code.
Sub Unhide_Non_Adjacent_Rows()
Worksheets("Unhide Non Adjacent Rows VBA").Range("10:10,12:12,15:15").EntireRow.Hidden = False
End Sub
Here, I have created a Sub Procedure Unhide_Non_Adjacent_Rows and mentioned the worksheet that I am going to work with. I have used the Range.Hidden property and set it False as I want to unhide the entire row. Ranges from (“10:10,12:12,15:15”) indicate that I have selected the 10th, 12th, and 15th row.
Then run the program. Excel will unhide the 10th, 12th, and 15th rows.
Note: I have used a line break at the 2nd line. It is optional. The code will run too if you do not use the line break.
3.4. Unhide All Rows in Worksheet using VBA
Now, I am going to show how to unhide all the rows in a worksheet.
To open the VBA editor and to insert a new module follow the steps explained in section 3.1.
After inserting a new module
Then write down the following code.
Sub Unhide_All_Rows()
Worksheets("Unhide All Rows VBA").Rows.EntireRow.Hidden = False
End Sub
Here, in the Sub Procedure Unhide_All_Rows, I have used the Worksheet.Cells Property and set the property False to unhide all the rows in the worksheet.
Then run the program. Excel will unhide the 5th, 7th, 8th, 10th, 12th, and 15th rows in the datasheet.
Read More: [Fixed!] Excel Rows Not Showing but Not Hidden
Practice Workbook
Lastly, I have attached a practice worksheet for you to practice. You can use that sheet and practice to master the skill.
Download Practice Workbook
Conclusion
In this article, I have explained all possible ways to unhide rows in Excel in shortcut ways. I will be glad if anyone finds this article helpful. Moreover, if you have any queries or suggestions, feel free to share them in the comment box.
Related Articles
- VBA to Hide Rows in Excel
- VBA to Hide Rows Based on Cell Value in Excel
- VBA to Hide Rows Based on Criteria in Excel
- How to Hide Blank Rows in Excel VBA
- How to Automatically Hide Rows with Zero Values in Excel
- How to Unhide Rows in Excel
- How to Unhide Top Rows in Excel
- [Fix]: Unable to Unhide Rows in Excel
