You can hide rows and columns in your Excel sheet if you do not want the data to be visible but at the same time don’t want to delete them. In this tutorial article, you will learn how to delete hidden rows in Excel.
Deleting the hidden rows does not just removes them from the active sheet, but also from the entire workbook.
How to Delete Hidden Rows in Excel
Below are the methods you can employ to delete all the hidden rows from the workbook in Excel:
Method #1. Using the Inspect Document Option.
The Inspect Document option will display the number of hidden rows, and from there you can delete them. Before proceeding with these steps, you have to save your workbook before running the Document Inspector option, as the data changed by these steps can not be recovered after. Excel will bring up a warning prompt asking you to save the document if it is not saved already.
Step #1: Click the File tab option in your worksheet.
Step #2: When the options are open, click on ‘Info’.
Step #3: Click on the ‘Check for Issues’ option.
Step #4: Then select the ‘Inspect Document’ option.
Step #5: The document Inspector dialog box will open, then you tick the ‘Inspect’ button.
Step #6: Excel will inspect the entire workbook and give you the information about the workbook.
Step #7: Scroll down and you will find the ‘Hidden Rows and Columns’ option.
Step #8: The total number of hidden rows it has found in the workbook will be displayed. So from here click on the ‘Remove All’ button.
All the hidden rows in this workbook will be deleted.
If there are no hidden rows, you would see a green tick before the ‘Hidden and Rows and Columns’ box indicating no hidden rows were found.
Method #2. Use the VBA Method to Delete Rows and Columns
With the Visual Basic Editor, you can delete multiple hidden rows and columns. You get to use VBA macro codes to automate this process.
Once you have the code to run the process, place this VBA code in the module in the Visual Basic Editor. You can then run the code directly from the Visual Editor, using the macro dialog box, or add this macro to the quick access toolbar.
Step #1: Open the ‘VBA Editor’ using the shortcut command of ALT+F11.
Or, open the ‘Developer Tab’ and click on ‘Visual Basic’ in the menu.
Step #2: Click on ‘Insert’ and then ‘New Module’ to insert a new module in VBA.
Step #3: Paste the code that commands the sheet to remove page breaks.
Step #4: Run the code and exit the VBA Editor.
You can also add the code to your personal macro workbook so that you can access it easily especially if you need to use it often.
How to Delete Hidden Rows in Excel Shortcut
There is no known shortcut to enable you to delete hidden rows in Excel. Instead create a macro VBA code and save it so that it helps you unhide and delete hidden rows and columns from your Excel sheet.
Applying Filter to Unhide Rows
To unhide rows in Excel:
Step #1: Go to the ‘Home Tab’.
Step #2: In the ‘Cells’ group , click the ‘Format’ button.
Step #3: Navigate to ‘Hide & Unhide’ under ‘Visibility’.
Step #4: Select ‘Unhide Rows’.
Or press these keys simultaneously on your keyboard to unhide rows: Ctrl + Shift + 9.
Before you go…
Finally, we have successfully deleted hidden rows from Excel. Check out our recommended articles on Microsoft Excel and its many features:
FAQs
How to delete hidden columns in Excel?
Step #1: Click the File tab option in your worksheet.
Step #2: When the options are open, click on ‘Info’.
Step #3: Click on the ‘Check for Issues’ option.
Step #4: Then select the ‘Inspect Document’ option.
Step #5: The document Inspector dialog box will open, then you tick the ‘Inspect’ button.
Step #6: Scroll down and you will find the ‘Hidden Rows and Columns’ option.
How to delete hidden columns in Excel using Mac?
Step #1: Click the File tab option in your worksheet.
Step #2: When the options are open, click on ‘Info’.
Step #3: Click on the ‘Check for Issues’ option.
Step #4: Then select the ‘Inspect Document’ option.
Step #5: The document Inspector dialog box will open, then you tick the ‘Inspect’ button.
Step #6: Scroll down and you will find the ‘Hidden Rows and Columns’ option.
The total number of hidden rows and columns it has found in the workbook will be displayed. So from here click on the ‘Remove All’ button. All the hidden columns in this workbook will be deleted. Or use a designated VBA code to delete hidden columns in Excel.
How to unhide all hidden rows in Excel?
Step #1: Go to the Home Tab.
Step #2: In the Cells group, click the Format button
Step #3: Navigate to Hide & Unhide under Visibility.
Step #4: Select Unhide Rows.
Or, press these keys simultaneously on your keyboard to unhide rows: Ctrl + Shift + 9.
How to show hidden data in Excel?
Step #1: Go to the Home Tab.
Step #2: In the Cells group, click the Format button.
Step #3: Navigate to Hide & Unhide under Visibility.
Step #4: Select Unhide Rows or Unhide Columns.
All the hidden data in your worksheet will now become
