Skip to main content

Delete all hidden rows or columns in Excel - 3 easy ways

Author: Xiaoyang Last Modified: 2025-04-16

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 selected range / 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:

A screenshot of the Inspect Document option in Excel to delete hidden rows and columns

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:

A screenshot of the Document Inspector dialog box showing hidden rows and columns

3. And then click "Close" button to close the dialog. And all of the hidden rows and columns in the workbook will be removed.

Additional Tips:
  • 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.

Additional Tip:
  • 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 selected range / worksheet / whole workbook with Kutools for Excel

With Kutools for Excel, you can effortlessly delete all hidden rows and columns within a selected range, a worksheet, or even the entire workbook in just a few clicks, saying goodbye to manual searches and complex procedures.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

If you have installed Kutools for Excel, please do as this:

1. Click "Kutools" > "Delete" > "Delete Hidden (Visible) Rows & Columns", see screenshot:

A screenshot of the Delete Hidden Rows and Columns option in Excel

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:

A screenshot of the Delete Hidden Rows and Columns dialog box

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

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 selected range / worksheet / whole workbook

 
Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Related Article:

How to delete hidden worksheets in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!