How to Delete Hidden Rows in Excel (The Right Way)

Hidden rows can be a reason for confusion and mistakes when working with a large data set.

Regardless of why and how you hid the rows, they can be a pain to deal with when making data analysis.

You need to know how to get rid of hidden rows in Excel to maintain a clean and organized spreadsheet.

In this tutorial, we will see different ways as to how you can delete hidden rows in Excel. Download our sample workbook here to practice along the guide.

Table of Contents

Delete hidden rows from the worksheet

If you don’t want to work with all your workbook but just delete hidden rows from your worksheet, this is exactly what you need.

We will use the following sample data to remove hidden rows from the worksheet.

To do that,

Step 1) Go to the Home tab and click on the Find & Select button.

Step 2) From the dropdown that appears, select Go to Special.

Step 3) The Go to Special dialog box will appear.

Step 4) Select Visible cells from the options.

Step 5) Press Ok.

All visible cells will be selected in your sheet.

Step 6) Press CTRL + C to copy all visible cells to your clipboard.

Step 7) Open a new worksheet and press CTRL + V to paste.

And your work’s finished. All hidden rows will be deleted from your worksheet with only the visible cells present on your worksheet.

How cool is that?

Delete hidden rows from the workbook

The most common method to delete hidden rows from a workbook is to use the Inspect feature of Excel. Its primary purpose is to remove filtered or hidden data from your workbook so no confidential data is being shared without your knowledge

We will use the same sample data set as earlier. We want to remove all the hidden rows in our workbook.

To do that,

Step 1) Click on the File tab in the Excel workbook.

Step 2) Select Info from the options that appear.

Step 3) Click on the Check for Issues dropdown next to Inspect Workbook.

Step 4) Select Inspect Document from the dropdown menu.

Step 5) Excel will show a prompt.

Step 6) Press Ok.

Step 7) The Document Inspector dialog box will appear on the screen.

Step 8) Make sure the Hidden Rows and Columns option is selected.

Step 9) Click the Inspect button.

If there are any hidden rows, the dialog box will show a Remove All button next to the Hidden Rows and column option.

Step 10) Select Remove All to remove them.

Step 11) Press Close.

And it’s done!

All hidden rows and columns from your workbook have been deleted.

Delete hidden rows using VBA

Another cool way to delete hidden rows in your worksheet is to use Excel VBA. The process includes writing VBA code for running the macro but in this case, we got that all ready for you.

Let’s see how you can use VBA to delete hidden rows in different ways.

From a workbook

We will use the same sample data set as earlier. To delete hidden rows from a worksheet,

Step 1) Press Alt + F11 in your Excel workbook to open Visual Basic editor.

Step 2) Click on the Insert tab and select Module from the dropdown.

Step 3) The Module window will appear.

Step 4) Paste the following code in the window.

Step 5) Close the editor.

Step 6) Press Alt + F8 to open the Macros window.

Step 7) Select DeleteHiddenRowsFromWorkbook() macro.

Step 8) Press Run.

Voila! All hidden rows from your workbook will be deleted.

Cool, no?

From a specific range

We will use the same sample data set as earlier. To delete hidden rows from a specific range,

Step 1) Press Alt + F11 in your Excel workbook.

Step 2) Click on the Insert tab and select Module from the dropdown.

Step 3) The Module window will appear.

Step 4) Paste the following code in the window.

Step 5) Close the editor.

Step 6) Press Alt + F8 to open the Macros window.

Step 7) Select DeleteHiddenRowsFromRange() macro.

Step 8) Press Run.

Tada! All hidden rows from the selected range will be deleted.

How easy was that?

With no data

We will use a dataset with no data in hidden rows.

To delete hidden rows with no data in them,

Step 1) Press Alt + F11 in your Excel workbook.

Step 2) Click on the Insert tab and select Module from the dropdown.

Step 3) The Module window will appear.

Step 4) Paste the following code in the window.

Step 5) Close the editor.

Step 6) Press Alt + F8 to open the Macros window.

Step 7) Select DeleteHiddenRowsWithNoData() macro.

Step 8) Press Run.

And it’s done! All hidden rows with no data will be deleted from the worksheet.

How easy was that?

With specific text

We’ve seen how to delete hidden rows from a specific range. Let’s now see how you can do that with rows containing specific text.

To do that,

Step 1) Press Alt + F11 in your Excel workbook.

Step 2) Click on the Insert tab and select Module from the dropdown.

Step 3) The Module window will appear.

Step 4) Paste the following code in the window.

Step 5) Close the editor.

Step 6) Press Alt + F8 to open the Macros window.

Step 7) Select DeleteHiddenRowsWithSpecificText() macro.

Step 8) Press Run.

Voila! All hidden rows containing the said text will be deleted from the worksheet.

Cool, no?

Conclusion

In this guide, we saw different ways of deleting hidden rows in Microsoft Excel. We saw how to delete rows in your workbook. We also saw how you can delete specific rows or ones with no data in them.

Our methods also included writing VBA code – a slightly difficult method but very efficient. You can use any of the above methods as desired.

To learn more about Excel rows and columns, give the articles below a read.

How to Combine Rows in Excel (Without Losing Data)

How to Delete Multiple Rows in Excel (The Right Way)

How to Unhide All Hidden Rows & Columns in Excel (At Once)

We hope you enjoyed reading this as much as we did crafting it!