How to Remove All Hidden Rows or Columns in Excel

Have you ever hidden rows or columns in Microsoft Excel? Almost all Excel users have probably done it.

Many Excel users hide rows or columns that they do not want to show for various reasons. They hide for a while that the data display looks good.

This is one way to keep useful data visible, and there is no need to delete data you do not need to be visible.

When the data is ready for you to publish or ready for you to share, the hidden rows or columns will appear annoying. If there are a lot of hidden rows or columns, deleting them manually is a time-consuming task.

To solve this, Excel has a built-in function of finding and removing hidden rows and columns at once. Here are some easy and quick steps to remove all hidden rows or columns in Excel.

Remove All Hidden Rows or Columns in Excel with Inspect Document

1. Click the “File” tab on the Microsoft Excel menu.

2. Then click the “Info“ button. In the “Inspect Workbook” section, click the “Check for Issues” button. Next, select the “Inspect Document” option. If you have not saved the file, it will be a warning to save the file first.

3. In the “Document Inspector“ dialog box, click the “Inspect” button.

4. Then swipe down and look for “Hidden Rows and Columns“. Next, click on the “Remove All” button.

Be careful in doing so. You can not return to the previous with the “Undo” process.

Remove All Hidden Rows or Columns in Excel with VBA

If you don’t know how to use VBA in Microsoft Excel, you can look at our other articles. How to Make Simple Macros in Excel.

The following VBA script will delete all rows and columns in a Worksheet.

Sub DeleteHiddenRowsColumns()
Dim sht As Worksheet
Dim LastRow as Integer
Dim LastCol as Integer
Set sht = ActiveSheet
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
For i = LastRow To 1 Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
For i = LastCol To 1 Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End Sub