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.