Sometimes while working on Excel data, users hide multiple rows and columns that they don’t want to be visible, and later after making the data ready, deleting those multiple hidden rows and columns within the data is a time-consuming task.
To overcome this Excel has an inbuilt function to find, and delete the hidden rows and columns in one go. Here are some easy and quick steps to delete all hidden rows or columns in Excel.
Delete All Hidden Rows or Columns in Excel Using Inspect Document
- First, click on the “File” tab.
- After that click on the “Info” from the pop-up menu and then click on “Check for Issues” and then “Inspect Document”.
- Once you click on “Inspect Document”, Excel opens the “Document Inspector” menu.
- Now, you just need to click on the “Inspect” button.
- In the end, click on “Remove All” in the “Hidden Rows and Columns” selection.
- At this point, all the hidden rows or columns in Excel have been removed.
Delete All Hidden Rows or Columns using VBA
In the following two codes, you can delete rows and columns from the used range which are hidden.
Sub delete_hidden_columns() Dim ws As Worksheet Dim iCol As Integer Set ws = ActiveSheet iCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column For i = iCol To 1 Step -1 If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete Next End Sub '------------------------------------------------- Sub delete_hidden_rows() Dim ws As Worksheet Dim iRow As Integer Set ws = ActiveSheet iRow = ws.UsedRange.Columns(ws.UsedRange.Rows.Count).Row For i = iRow To 1 Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete Next End Sub