This post will guide you how to delete all hidden rows or columns in Excel 2010/2013/2016. How do I efficiently delete all hidden columns and rows in a worksheet in Excel. How to delete all hidden rows or columns with VBA macro in Excel.

Delete All Hidden Rows or Columns in Excel

---

If you want to delete all hidden rows or columns in Excel, just do the following steps:

#1 click File tab, and select Info menu from the popup menu list.

#2 click Check for Issues button in the Info page. And select Inspect Document from the drop-down list. The Document Inspector dialog box will appear.

#3 click Inspect button in the Document Inspector window.

#4 click Remove All button in the Hidden Rows and Columns section.

Delete All Hidden Row or Column with VBA Macro

---

You can also use an excel VBA macro to delete all hidden rows and columns in your worksheet. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub deleteAllHidden()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Dim lastRow
        lastRow = 4000
        For iCntr = lastRow To 1 Step -1
            If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete
        Next
    Next ws
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.