Delete Hidden Rows in Excel

HomeExcel BasicsDelete Hidden Rows in Excel

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

  1. First, click on the “File” tab.
    1-file-tab
  2. After that click on the “Info” from the pop-up menu and then click on “Check for Issues” and then “Inspect Document”.
    2-inspect-document
  3. Once you click on “Inspect Document”, Excel opens the “Document Inspector” menu.
  4. Now, you just need to click on the “Inspect” button.
    3-document-inspector
  5. In the end, click on “Remove All” in the “Hidden Rows and Columns” selection.
    4-remove-all
  6. At this point, all the hidden rows or columns in Excel have been removed.
    5-hidden-rows-and-columns

Delete All Hidden Rows or Column 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