How To Delete Hidden Rows in Excel

In this article, we talk about how to Delete Hidden Rows in Excel. When working in an Excel spreadsheet, you may want to get rid of rows that aren’t being used. A user of Excel can delete hidden rows in a number of ways, such as by using the “File” tab or a VBA macro.

If you know how to delete hidden rows in Excel, you can get rid of rows you no longer need and clean up your worksheet. Most likely, an Excel sheet has useless values and data that you don’t need for anything. This is why people often hide them to make the data easier to understand. If you hide many rows and columns, you won’t be able to see them again for a while.

However, you may need to delete hidden rows in Excel from time to time. It makes no sense to keep some of the information hidden on the sheet when it is no longer needed. You need to get rid of it as soon as possible because it will only make your file bigger and less useful. It wouldn’t be easy to get rid of those hidden rows and columns.

Ways To Delete Hidden Rows in Excel

Using VBA Code to Delete Hidden Rows

This is a great way to get rid of hidden rows and columns if you only need to do it on one sheet and not the whole workbook. It might not be as powerful as the “Inspect Document” feature, but it’s a quick and easy way to delete rows from a worksheet. Here’s what you need to do:

• Open the Excel file and press Alt + F11 to open VBA Editor.
• Now, go to Insert > Module.
• 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

• Then, press F5 to run the code.

Using the Inspect Document Function

Excel, Word, PowerPoint, and Visio all have the Document Inspector tool. It’s the best way to find and get rid of any hidden information in a document. This is helpful when you have to share documents with other people and want to make sure there are no surprises in the document.

Excel makes it easy to get rid of rows and columns that aren’t being used. You can also go to the official Microsoft Excel support site to know more information about this. What you need to do is:

• Open the workbook you want to use, and go to File.
• Now, click on Info.
• Next, click on the Check for Issues button, then select Inspect Document.
• Within the Document Inspector dialog box, make sure that Hidden Rows and Columns is selected and then click Inspect.
• 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.

Using a Third-Party Software

There are many third-party tools that can help you quickly get rid of hidden rows and columns. Most of the time, they add to Excel by giving the toolbar more options. In addition to this, they have other features that may be hard to find, such as:

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