How To Delete All Hidden Rows In Excel

Mastering Excel takes a lot of time and effort. Unless you’re an expert, getting a hang of the advanced features might be a daunting process. Unfortunately, not all commands are clearly visible within the interface.

Deleting hidden rows is a perfect example. In the older versions of Excel, this wasn’t even possible. Thankfully, Microsoft decided to add it to versions 2007 and newer. Still, the way it works is a mystery to many.

There are two ways of deleting hidden rows in Excel. Let’s go over them.

Using the Inspect Document Function

The Document Inspector feature is available in Excel, Word, PowerPoint, and Visio. It’s the best way to uncover and delete any hidden data that might be present in a document. This is useful when you have to share documents with other people and want to make sure there are no surprises in the document.

In Excel, deleting hidden rows and columns is an easy job. Here’s what you have to do:

1. Open the workbook you want to use, and go to File.
2. Now, click on Info.
3. Next, click on the Check for Issues button, then select Inspect Document.
4. Within the Document Inspector dialog box, make sure that Hidden Rows and Columns is selected and then click Inspect.
5. The Document Inspector will show you a report which shows you whether there are any hidden rows and columns. If there are, go to Remove All, then click Cancel.

Deleting Hidden Rows on Previous Versions of Excel

This feature can be found in the same place in Excel 2013 and 2016. The interface of the 2010 version looks a bit different, but the pathway to the Document Inspector is the same. If you’re using Excel 2007, you can find this option by clicking on the Office button, then going to Prepare > Inspect Document.

There’s not a difference to the feature itself, so this will make sure that there are no hidden rows and columns regardless of your version of Excel.

Using VBA Code to Delete Hidden Rows

This is a very convenient method if you only need to remove hidden rows and columns from one sheet instead of a whole workbook. It might not be as capable as the Inspect Document feature, but it’s a very easy and fast way of deleting rows from a worksheet. Here’s how to do it:

1. Open the Excel file and press Alt + F11 to open VBA Editor.
2. Now, go to Insert > Module.
3. When the module window pops up, paste the following code in it:

Sub deletehidden()
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

1. Then, press F5 to run the code.

This will remove all hidden rows and columns from the sheet you’re working on. If there aren’t many of them, you can repeat this process to clear the entire workbook of them in no time.

The only issue that can occur is if there are any formulas in the hidden rows and columns. If they affect the data within a sheet, deleting them might cause some of the functions to not work properly, and you might end up with some incorrect calculations.

Using a Third-Party Software

There’s a variety of third-party solutions that can help you remove hidden rows and columns in an instant. They usually serve as an extension to Excel, adding more options to the toolbar. Aside from this, they also offer additional features that might be hard to find, such as:

1. Batch delete checkboxes
2. Batch delete option buttons
3. Delete blank rows,
4. Batch delete all macros

They may prove very useful if you’re a heavy Excel user and are looking for quick solutions to the common issues that Microsoft has yet to address properly.

The Final Word

The fact that Excel is equipped with the ability to discover and delete hidden rows and columns makes it much more convenient to use. Thanks to Document Inspector, there’s no need to worry about hidden information.

If you find coding fun, executing VBA code is a more convenient solution, which you can do even if you don’t have any coding experience. All you have to do is to cut and paste the right commands.

Lastly, you can choose a third-party solution that will help you get the most out of Excel. They can be a great upgrade to the software and make it much easier to use.