Delete all hidden rows or columns in Excel - 3 easy ways
Managing large datasets in Excel often involves hiding rows or columns to simplify the view. However, there are times when you need to delete these hidden rows or columns permanently. This guide will walk you through several methods to achieve this efficiently.
Delete all hidden rows and columns in a workbook with Inspect Document function
Delete all hidden rows and columns in active worksheet with VBA code
Delete all hidden rows or columns in a selection / worksheet / whole workbook with Kutools for Excel
Delete all hidden rows and columns in a workbook with Inspect Document function
The Inspect Document function in Excel provides a simple way to find and remove these hidden rows and columns. Here’s a step-by-step guide to optimize your Excel workbook by deleting hidden rows and columns using the Inspect Document function.
1. Click File > Info > Check for Issues > Inspect Document, see screenshot:
2. Then a Document Inspector dialog box will appear, click Inspect button, and the hidden rows and columns in the whole workbook have been inspected, click Remove All button, see screenshot:
3. And then click Close button to close the dialog. And all of the hidden rows and columns in the workbook will be removed.
- Backup Your Data: Always keep a backup copy of your workbook before using the Inspect Document function, as the deletion of hidden rows and columns is irreversible.
- Review Data Thoroughly: Before deleting, make sure that the hidden rows and columns do not contain essential data that you might need later.
- Remove from the whole workbook: This will permanently delete all hidden rows and columns from your workbook.
- Formula error: If there are formulas to manipulate the data in your workbook and the formulas call for data from hidden rows and columns, then deleting that information will cause incorrect result.
Delete all hidden rows and columns in active worksheet with VBA code
If you just want to remove the hidden rows or columns in a worksheet, the following VBA code can help you. Please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Delete all hidden rows and columns of active worksheet:
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
2. Then press F5 key to execute the code. And all the hidden rows and columns have been deleted in the active worksheet.
- Formula error: If there are formulas to manipulate the data in your workbook and the formulas call for data from hidden rows and columns, then deleting that information will cause incorrect result.
Delete all hidden rows or columns in a selection / worksheet / whole workbook with Kutools for Excel
With Kutools for Excel, you can effortlessly delete all hidden rows and columns within a selection, a worksheet, or even the entire workbook in just a few clicks, saying goodbye to manual searches and complex procedures.
If you have installed Kutools for Excel, please do as this:
1. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns, see screenshot:
2. In the Delete Hidden (Visible) Rows & Columns dialog box, specify the scope that you want to apply the operation from Look in drop down list. (If you choose In Selected Range, you must select a range that you want to use first.) And then check Rows or Columns under Delete type. And select Hidden rows from Detailed type. Then click Ok, the hidden rows have been deleted from the selected range, see screenshot:
Deleting hidden rows or columns in Excel can streamline your data and improve worksheet management. Whether you prefer using Excel’s built-in features, a VBA macro, or the convenience of Kutools for Excel, each method provides a robust solution for maintaining clean and efficient datasets. Choose the method that best suits your workflow and enhance your Excel proficiency. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Demo: Delete all hidden rows or columns in a selection / worksheet / whole workbook
Related Article:
How to delete hidden worksheets in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
