How to Find Missing Rows in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Handy Ways to Find Missing Rows in Excel


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.

Step 1:

  • You can see that rows 6th through 8th are missing in this section, but we aim to find all of the rows.

Applying Unhide Command to Find Missing Rows in Excel

Step 2:

  • 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.

Step 3:

  • Here, all the missing rows are, at last, available.

Read More: How to Deal with Missing Data in Excel (6 Suitable Ways)


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.

Step 1:

  • In this part here, you will see the rows from 6th to 8th are missing but we want to make all the rows accessible.

Utilizing Format Tool to Find Missing Rows in Excel

Step 2:

  • 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.

Step 3:

  • All the missing rows are finally available here.

Read More: How to Filter Missing Data in Excel (4 Easy Methods)


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.

Step 1:

  • 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.

Applying VBA Code to Find Missing Rows in Excel

Step 2:

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.

Step 3:

  • 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

Applying VBA Code to Find Missing Rows in Excel

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.

Step 4:

  • Finally, you will find all the missing rows visible here.

Read More: How to Find Missing Values in Excel (3 Easy Ways)


Conclusion

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.


Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo