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 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:

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.

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.

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.

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

Related Article:

How to delete hidden worksheets in Excel?