How to Delete a Hidden Row or Column in Excel

Excel’s Hide Rows and Columns feature comes in handy to declutter your worksheet and keep unwanted data out of sight. While it’s incredibly useful, hiding too many rows and columns can take up a lot of space. In such case, you can permanently get rid of them.

It is possible to manually unhide and delete ranges, but you can have a hard time while working with large data. But there are two other ways to eliminate hidden rows or columns in your spreadsheet. 

Using Document Inspector

Document Inspector is mostly used to examine the workbook with selected contents. This tool does all the heavy lifting for you.

It scans the number of hidden rows/columns in a workbook and gives you a Remove All option to delete them. This is by far the easiest way to delete all hidden ranges at once. 

1. On your Excel worksheet, click on File Tab at the top-left corner.
   
2. Click Info menu.
   
3. On the Inspect Workbook, select Check for Issues > Inspect Document. 
   
4. Pick Yes in the confirmation pop-up.
   
5. Now, on the Document Inspector box, choose Inspect.
   
6. Scroll to find Hidden Rows and Columns menu. Then, Click on the Remove All option next to it.
   

Note: The Remove All option permanently deletes all hidden rows and columns at once. That's why we recommend that you keep a data backup in case you need it later. 

Using VBA

With the Document Inspector, you cannot choose to delete hidden rows or columns from a specific area. In such case, use the Visual Basic Analysis (VBA) tool. Here, you can add a VBA code to remove hidden ranges quickly.

To use VBA, you must activate Developer Tab first. However, you can skip Step 1 if you already have the developer menu on your worksheet.

Step 1: Activate Developer Tab

1. Navigate to the File tab on your worksheet.
   
2. Choose Options menu at the bottom-left.
   
3. Click on Customize Ribbon menu on the left panel.
   
4. Check the box for Developer and click OK.
   

Step 2: Run Code

1. On your worksheet, click on the Developer tab.
2. From the Code section, choose Visual Basic.
   
3. On Microsoft Visual Basic for Applications, go to Insert tab > Module.
   
4. Enter one of the following codes in the empty field:

To delete entire hidden rows/columns from the 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

To delete hidden rows from a specific range:

Here, you can replace the cell range “B2:E8” with the specific range area you want to delete.

Sub DeleteHiddenRows()
Dim sht As Worksheet
Dim Rng As Range
Dim LastRow As Integer
Dim RowCount As Integer
Set sht = ActiveSheet
Set Rng = Range("B2:E8")
RowCount = Rng.Rows.Count
LastRow = Rng.Rows(Rng.Rows.Count).Row
For i = LastRow To LastRow - RowCount Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
End Sub

5. Click on Run icon or press F5 to run the code.
6. Close the VBA window.

Related Questions

How to Know if I Have Hidden Rows and Columns in the Spreadsheet?

With the Go to Special menu of Excel, you can easily track down hidden Rows and Columns. For this, after selecting cell ranges, press F5 > Special. Click on Visible cells only and hit OK button. If there are hidden columns and rows, you will see a White border in the adjacent area. 

Can You Undo the Deleted Rows and Columns?

You can always undo deleted rows and columns of your spreadsheets by entering Ctrl + Z keys. Unfortunately, this is not possible for hidden cells. Once you delete hidden rows and columns, there’s no way to undo them or retrieve them back.

Can Deleting the Hidden Rows and Columns Affect the Integrity of the Database?

As long as hidden rows and columns do not contain any valuable information, deleting them will not affect the integrity of the database. But, if the hidden cells were either Dependent cells or Precedent cells, you’ll get a VALUE! Or #REF! error. Meaning, cells containing or referring to formulas.

Is There a Function to Automatically Delete Hidden Cells?

There is no Excel function to automatically delete hidden cells. The only way to delete them is from the Document Inspector or using the VBA code.