When creating Worksheets, operations like inserting, erasing, missing, and finding missing rows or columns can be very beneficial. You can focus on the data you need to work with by hiding rows in Excel to limit the number of data or empty rows on your screen. It’s crucial to know how to unhide your rows so you can keep track of your data and use it as needed. In this article, we will show you how to find missing rows in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
3 Handy Ways to Find Missing Rows in Excel
When working in a group, you might share, amend, or double-check data by sending Excel files back and forth. In order to simplify their perspective of the data and hide unnecessary rows, calculations, or anything that would make the file harder to read, your teammates may have concealed rows while working on the file.
By examining the space between rows to find brief blank portions or by using other Excel tools, you can locate missing or hidden rows. In the following methods, you will learn to find missing rows in Excel by applying the Unhide command, utilizing the Format Tool, and applying VBA Code. Let’s suppose we have a sample data set.
1. Applying Unhide Command to Find Missing Rows in Excel
In this very first method, we will be applying the Unhide command in Excel to find missing rows in our data set. This is a somewhat easy way to find missing Excel rows by right-clicking on any of the sheets to open the unhide command.
- You can see that rows 6th through 8th are missing in this section, but we aim to find all of the rows.
- First, hold down the Shift key and select the fifth through ninth rows.
- Now, right-click on the sheets available here to open the dialog box below.
- Then, choose the Unhide command.
- Here, all the missing rows are, at last, available.
2. Utilizing Format Tool to Find Missing Rows in Excel
In this part, you will learn to find missing Excel rows using the Format tool from the Home tab. Although this strategy is more time-consuming than the one mentioned above, it is always a good idea to be aware of other methods.
- In this part here, you will see the rows from 6th to 8th are missing but we want to make all the rows accessible.
- Firstly, select the rows from the 5th to 9th rows by holding the Shift key.
- Secondly, go to the Home tab.
- Thirdly, choose the Format tool.
- Then, open the Hide & Unhide command.
- Now, click on the Unhide Rows option.
- All the missing rows are finally available here.
3. Applying VBA Code to Find Missing Rows in Excel
VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to find missing rows.
- Here, you can see the 6th, 7th, and 8th rows are missing but we want to find all the missing rows.
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
- Now, we will find these missing rows by applying the VBA code.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write a VBA code.
- Now, paste the following VBA code into the Module.
- To run the program, click the “Run” button or press F5.
Sub Find_Missing_Rows() 'To find missing rows 6 to 8 Rows("6:8").EntireRow.Hidden = False 'This statement allows the missings rows to be visible Rows.EntireRow.Hidden = False End Sub
VBA Code Breakdown
- Firstly, we specify a name for the subject as Find_Missing_Rows().
- Secondly, we will select a range from the 6th to the 8th row using this statement as Rows(“6:8”).EntireRow.Hidden = False.
- Finally, we apply a statement that allows us to find the missing or hidden rows as EntireRow.Hidden = False.
- Finally, you will find all the missing rows visible here.
In this article, we’ve covered 3 handy methods to find missing rows in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
- How to Interpolate Missing Data in Excel (4 Ways)
- Find Missing Values in a List in Excel (3 Easy Methods)
- Count Missing Values in Excel (2 Easy Ways)
- How to Fill Missing Values in Excel (5 Useful Tricks)
- Compare Two Excel Sheets to Find Missing Data (7 Ways)
- How to Cross Reference in Excel to Find Missing Data (6 Ways)
- Compare Two Columns in Excel for Missing Values (4 ways)