How To Delete All Hidden Rows And Columns In MS EXCEL

 

Excel

To become a master in Excel you have to get a hang of advance feature which might be a daunting process. It takes a lot of time to master Excel as all commands are not clearly visible within the interface.

Deleting any hidden rows and columns are a perfect example. Microsoft added this feature in version 2007 and newer and this was not possible in its previous versions.

There are two ways to delete hidden rows and columns in Excel.

Using The Inspect Document Function

The Document Inspector feature is accessible in Excel, Word, PowerPoint, and Visio. It’s the most ideal approach to reveal and erase any hidden data that may be available in a record.

To Delete Hidden Rows  and Columns
  1. Open the File And go to Info
  2. Click check  for issue button and select INSPECT DOCUMENT
  3. Make sure the Hidden Rows and Columns are selected
  4. The DOCUMENT INSPECTOR will show a report, if there are any hidden rows or columns or not
  5. If there are any click on remove all

This feature can be found in a similar spot in Excel 2013 and 2016. The interface of the 2010 adaptation looks somewhat changed, yet the pathway to the Document Inspector is the equivalent. In case you’re utilizing Excel 2007, you can discover this by selecting the Office button, then go to prepare and click on Inspect Document

USING VBA CODE
  1. Open the Excel file and press ALT+F11
  2. Select Insert from the menu bar and from the drop-down list select module
  3. After the module window pops up, Paste the following code:

Sub deletehidden()
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

Press F5 to run the code

This will erase every hidden rows and column from the sheet you’re dealing with. In the event that there aren’t a significant number of them, you can rehash this procedure to clear the whole exercise manual of them in the blink of an eye.

The main issue that can happen is if there are any recipes in the hidden rows and columns. In the event that they influence the information inside a sheet, erasing them may make a portion of the capacities not work appropriately, and you may finish up with some mistaken counts.

Leave a Reply

Your email address will not be published. Required fields are marked *