Delete All Hidden Rows or Columns in Excel

,

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.

delete all hidden rows or columns1

#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.

delete all hidden rows or columns2

#3 click Inspect button in the Document Inspector window.

delete all hidden rows or columns6

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

delete all hidden rows or columns3

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

delete all hidden rows or columns4

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.

delete all hidden rows or columns5

 

Leave a Reply